Between Statement Not Working Using Dates

Apr 5, 2006

Hi,

I am using an ADO connection to an access Db but am having from executing a query on the table.

In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;


SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'


The above works fine and produces results, but if I do the following - then it come up with error - data tyle mismatch;


SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'


Any Ideas Why? TIA

View Replies


ADVERTISEMENT

Between Statement Not Working Using Dates

Apr 5, 2006

Hi,

I am using an ADO connection to an access Db but am having from executing a query on the table.

In my table I have a Field named [Date] with Data Type as Date/Time (Short Date dd/mm/yyyy). When I perform a Query on the Db like;


SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] LIKE '28/03/2006'


The above works fine and produces results, but if I do the following - then it come up with error - data type mismatch;


SELECT [Date], [Part No], [Batch Qty] FROM [Speed Fastener Packing] WHERE [Date] = '28/03/2006'


Any Ideas Why? TIA

View 3 Replies View Related

Creating IIF Statement In Between Statement For Dates?

Apr 28, 2015

I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:

Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])

I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.

View 11 Replies View Related

IIF Statement Not Working

Aug 3, 2006

Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))

FixedExpenseOccurance is the the field and "*" is the values which get looked up from a table. I am trying to create an expense report. The query runs but no values are entered into the box?

For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.

View 10 Replies View Related

IIF Statement Not Working

Nov 23, 2007

hi i am trying to use the following iif statement in a query.

IIf([Forms]![MainForm]![cmb_ExcessStatus]="Resolved",1,IIf([Forms]![MainForm]![cmb_ExcessStatus]="Outstanding",2,IIf([Forms]![MainForm]![cmb_ExcessStatus]="All Records",([MainTable].[Status])=1 Or ([MainTable].[Status])=2,([MainTable].[Status])=1 Or ([MainTable].[Status])=2)))

So, if user selects 'Resolved' then use 1
if the user selects 'Outstanding' then use 2
if the user selects 'All records' then i want both 1 and 2 but this will not work. i also want the default to be 'All records'

Any help would be much appreciated.

View 1 Replies View Related

IIF Statement Not Working

Aug 3, 2006

Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))

I have this in a query and the query runs, it just doesnt do anything. FixedExpenseOccurance is the field name, "*" is the field values (they get looked up from a table). Any clues why this is not working?

For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.

View 8 Replies View Related

If Statement Not Working

Aug 10, 2006

(IIf((NextActivtion)>=(Today)),((DueToday)="yes"),((DueToday)="No"))

Says I have the incorrect number of Arguments? Any clues? These are all field name:
NextActivation (Is a date)
Today (is today's date)
DueToday (is a yes/no)

View 13 Replies View Related

Simple Sql Statement Not Working

Mar 2, 2008

i have an sql statement:

aa = "hello"

Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < aa ORDER BY [Tasks].Staff_Name;"


how can i make it look the the data i put in "aa" rather than prompt a msg box asking for "aa"?

View 5 Replies View Related

If Statement In A Query Not Working?

Oct 9, 2005

hi im trying to do the following if statement in a query but its not working, anyone know what im doing wrong?
any help would be great.

-------------------------------------------------------------------------
New: IFF [(TABLE-SALES]![PERIOD]<=[TABLE-AVERAGE]![ACTUALS_TO_PD] then [TABLE-SALES]![ACTUALS_TO_PD]) else
[TABLE-UPDATE]![FORECAST_1_BASE_VALUE]

------------------------------------------------------------------------
im trying to say if the field "period" in the table: "TABLE SALES" is the same or less than the value in the field "actuals to pd" in the table :"TABLE-AVERAGE" then the value displayed in this field ("new") should be the value: "actuals to pd" in the table "table-sales" otherwise the value in the field "forecast1 base value" in the table "table update"

View 4 Replies View Related

Insert Into Sql Statement Not Working.. Help!

Dec 5, 2006

hi guys i have a form that comes from a query with a parent and child table. Main table pk is pricingid and then the foreign key in tblHistory is pricingid.

I am trying to do an append table but i keep getting an error message
Pricing ID is the pk autonumber in tblPricing (parent table)
historyid is the pk in tblHistory and pricingid the fk(child table)
oldprice should be a currency value
the other fields are text.

this is my code:

strSQL = "Insert into tblHistory (DateChanged, Edit_UserName, OldPrice) Values(#" & now() & "#, '" & getusername() &"', " & Me.txtPrice.OldValue & ")


but i get the error message


You cannot or change a record because a related record is required in table tblPricing


any advise... i know i need to select somehow the pricingId from the parenttable, but how do i do that?

View 8 Replies View Related

Working With Dates

Mar 7, 2008

Hi there,

I have long file (1 mil records) downloaded from AS400 as .csv text file.

there 2 feilds - shipping date and receiving date.

I need to calculate the lead time in days as RecievingDate - (minus) ShippingDate.

It would be easy in Excel, but i have too many lines.

So there are 2 problems.

How to make text data from .CSV the date in Access?
How to calculate using date data in Access?

Thanks to everybody interested to help.

View 3 Replies View Related

Working With Dates

Dec 11, 2006

Hello,

I am trying to work out how to find the difference between two dates, in years and days. I have a [date_of_birth] field and another field [date] and wish to work out the difference in years and days as opposed to decimal years. Is it possible to do it?

Thanks.

View 1 Replies View Related

Modules & VBA :: If And NZ Statement Not Working In Access?

Jul 8, 2015

why this IF and Nz statement in my code below is not working. I know the me.txtrefNo value is "" (null) on a form field with property set to General Number. Therefore the Nz statement should return a "0" as I specified making the statement true as in 0 = 0 and then execute the actions below to generate a reference number however this it now happening as it's rendering the if statement as false and showing me the message " Whats going On?" which is after the else statement.

Code:
If Nz(Me.txtrefNo, 0) = 0 Then
Me.txtrefNo = DMax("[refNo]", "[R_P_Data_P]") + 1
tmpRefNo = Me.txtrefNo
'testing variable value
MsgBox (tmpRefNo)
Else
MsgBox ("What's going on?")
End If

PS. Does this have anything to do with the table field format that the me.txtrefNo value will be saved to?

View 2 Replies View Related

Working With Dates In Queries

Sep 15, 2005

I have set up an Attendance database at work, where a record is created for each member of staff when they are absent. The tables are set up as follows:

[tblStaff]: StaffID, FirstName, Surname, Team, JobTitle
[tblAbsence]: StaffID, StartDate, EndDate, Issue

What I want to be able to do is set up a query to search for a reason for absence on a certain date (btw my date format is set at dd/mm/yyyy). The problem I have is if the user needs to check a date in between the start and end date i.e. Start Date is 11/11/2005 end date is 11/12/2005 and the user checks on 01/12/2005 then this record will be found.

I know there is a simple way to do this but I can't seem to figure it out. Any help would be greatly appreciated!

View 2 Replies View Related

Between Dates Query Not Working

Apr 1, 2007

I have a query built by someone else that I think is the root of my problem. The query runs and selects Officers of an Insurance Company based on the dates in a table stating when they were officers. The problem is one of the date (enddate) is blank since he/she may be a current officer. The query reads ">[RPT_NAIC_AF1]![BeginDate] And <=[RPT_NAIC_AF1]![EndDate]" It is not returning all records after the start date which has no enddate. If I change the "And" to an "Or" I get the records I want, and a lot more so I know the problem lies here. Help please! I am a novice user but willing to learn.

View 14 Replies View Related

Working With Text And Dates

Mar 19, 2008

I have a situation where I need to extract the date from a text field to use in a select query. The date in the text field is listed at the beginning of the text field like this.

S12345678 3/21/2008 adittional text beyond here blah, blah...

the first series of numbers can be either 9 or 10 digits long, and then the date is always in this format, the length of the characters change accordingly with the date. So trying to use the Mid function only wont work.

I supposed I need to find a way to get the position of the space character in front of and after the date to use the Mid function for each record.

I need to be able to extract this date to do a DateDiff against another date.

I have been trying to do something like this.

Narr_Date: Format(Mid([si_narr_t],InStr(1,[si_narr_t],Chr(32)),InStr(9,[si_narr_t],Chr(32))),"Short Date")

But I know I am off the mark here. Can someone tell me what I am missing?

View 3 Replies View Related

Forms :: IIF Statement Within A Field In A Form Not Working

Jun 21, 2013

I am trying to created an IIF statement in a field I have in a form.

I have entered the following statement but it keeps coming up with the following error and I cannot see where I am going wrong.

The expression you entered contains invalid syntax, or you need to enclose your text data in quotes

=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100),IIf RenewalInstallment]<>0,(12-[MFAccepted])*[RenewalAnnualPremium]/12)*(1-[DiscountAccepted]/100)

If I enter the first part of the If statement i.e.

=IIf([RenewalInstallment]=0,(12-[MFAccepted])*[CurrentAnnualPremium]/12)*(1-[DiscountAccepted]/100)

The field calculates correctly however there seems to be something wrong with the nested if.

View 6 Replies View Related

General :: IIF Statement - Calculated Fields Not Working

Apr 8, 2015

Access 2013

I've created a table for a college project and realised that one of my calculated fields isn't working. It doesn't produce an error message, it just doesn't calculate.

IIf([CycleSpeed]<10,[CycleTime]*236,IIf(10<=[CycleSpeed]<12,[CycleTime]*354,IIf(12<=[CycleSpeed]<14,[CycleTime]*472,IIf(14<=[CycleSpeed]<16,[CycleTime]*590,IIf(16<=[CycleSpeed]<=20,[CycleTime]*708,IIf([CycleSpeed]>20,[CycleTime]*944,0))))))

View 6 Replies View Related

Use Dates Within If Statement

Nov 30, 2005

Hi all,

I'm trying to populate fields depending on the date entered in another field.

Can I use an If statement to determine whether the date is within a certain range?

eg.

If [Date] is between 01/01/2005 and 01/01/2006 Then
....

Any help greatly appreciated.

Chris

View 2 Replies View Related

Count Working Days Between 2 Dates

Nov 5, 2007

I've had it before, but can't find it.

basically something like this --

DateDiff("w", StartingDate, EndingDate)


that also makes sure date is not in tblHolidays.
anyone knows how to acomplish this ?

View 1 Replies View Related

Counting Working Days Between Dates

Feb 1, 2006

I would like to count the number of working days between two dates, NOT the calendar days, if the workers only work Monday thru Thursday. I know we could use the DateDiff function, but HOW do I count excluding Fridays, Saturdays, Sundays?

Thank you for your help.

View 1 Replies View Related

Queries :: DLookup Not Working With Certain Dates

Aug 30, 2014

Can't figure out why dlookup will not work for me in a query for certain dates?

Simple example to highlight the problem:

Table1 -

Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40

Query based on this table with the following calculated field:

Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")

Query output -

Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40

Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)

If you try it with many dates its skips several and I cannot see a pattern.

View 7 Replies View Related

Modules & VBA :: Working Days Between Two Dates

Jan 26, 2015

I am trying to carryout working days between two dates (Excluding weekends Friday & Saturday) but unable to fix it correctly, However i did search a lot over internet also about built in function but all is showing i have to add it module or macro etc.So, i select the module for Access 2010 but also not finish with function.Table name is "LeaveSettlement" and column name is "Total_Wdays", what field type i have to select for result column.

Public Function LeaveSettlement(ByRef Leave_Start As Date, _
ByRef Leave_End As Date _
) As Integer
' Returns the number of weekdays in the period from Leave_Start
' to Leave_End inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Friday and
' do not total two per week in number, this function will
' require modification.

[code]...

View 8 Replies View Related

Calculate Time Between Two Working Dates?

Oct 6, 2013

The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.

It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.

I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.

Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.

[code]...

View 14 Replies View Related

SQL Statement Won't Filter Out Dates?!

Oct 4, 2007

Hi all, thought i had finished this one but apparantly not yet :)

Firstly can anyone see a problem with this statement?

SELECT Final.* INTO NewTable FROM Final WHERE LoginDate <> #02/07/2007#

It works fine with non date/time values..

It's probably something silly i know but any help is appreciated...

Thanks

Mike

View 10 Replies View Related

Calculate Working Time Between Dates In Access

Oct 26, 2006

I have searched the forum for this answer but no luck. :(

I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.

I need this time to be calculated in hours.

In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.

At least if i could get the working days i could then convert it into hours.

Please help!

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved