Query Criteria where a single date comes from a form

[Guest]

I am trying to use an if statement in the query criteria that checks to see if the DateDiff is equal to 0. The date comes from a form, where the user enters a start date and an end date. I assume if start date and end date are the same that DateDiff equals 0. Is this correct? I have tried a numer of different versions of my query below. Most work when the start date and end date are different, but if they are the same date cannot find any recorsd in the table.

Anyone know what I am doing wrong? I would appreciate any help you can give me.

IIf(DateDiff("d",[Forms].[frmGetDates].[txtStartDate],[Forms].[frmGetDates].[txtEndDate])=0,[Forms].[frmGetDates].[txtStartDate],([Incident_Tickets].[Create-date]) Between [Forms].[frmGetDates].[txtStartDate] And [Forms].[frmGetDates].[txtEndDate])

 

[tina]

in the IIf function, i don't think you need to use the DateDiff function.
why not just say
IIf([Forms].[frmGetDates].[txtStartDate] =
[Forms].[frmGetDates].[txtEndDate], ...., ....)

hth

I am trying to use an if statement in the query criteria that checks to

see if the DateDiff is equal to 0. The date comes from a form, where the
user enters a start date and an end date. I assume if start date and end
date are the same that DateDiff equals 0. Is this correct? I have tried a
numer of different versions of my query below. Most work when the start
date and end date are different, but if they are the same date cannot find
any recorsd in the table.

Anyone know what I am doing wrong? I would appreciate any help you can give me.

IIf(DateDiff("d",[Forms].[frmGetDates].[txtStartDate],[Forms].[frmGetDates].
[txtEndDate])=0,[Forms].[frmGetDates].[txtStartDate],([Incident_Tickets].[Cr
eate-date]) Between [Forms].[frmGetDates].[txtStartDate] And
[Forms].[frmGetDates].[txtEndDate])

 

[Guest]

I finally figured out the problem. The date in the field in the table has minutes, seconds, and hundredths of seconds even though the field is a short date. In the form I was only entering month, day, year. I resolved the issue by using the left function in an expression in the query. Table DateField1:Left(TableDateField, 10). This just matches the first 10 characters in the field (01/01/2004).

The reason why it worked with the between statement is that even with the additional characters the values were between what I specified in the query.

--
Gary O

It still does not work. My main concerns is why will my query work with a date range, but not a single date? With a single date it returns no records. The primary difference between the a single date and a date range query is: one is using between and the other is not
--
Gary O

in the IIf function, i don't think you need to use the DateDiff function.
why not just say
IIf([Forms].[frmGetDates].[txtStartDate] =
[Forms].[frmGetDates].[txtEndDate], ...., ....)

hth

I am trying to use an if statement in the query criteria that checks to

see if the DateDiff is equal to 0. The date comes from a form, where the
user enters a start date and an end date. I assume if start date and end
date are the same that DateDiff equals 0. Is this correct? I have tried a
numer of different versions of my query below. Most work when the start
date and end date are different, but if they are the same date cannot find
any recorsd in the table.

Anyone know what I am doing wrong? I would appreciate any help you can give me.

IIf(DateDiff("d",[Forms].[frmGetDates].[txtStartDate],[Forms].[frmGetDates].
[txtEndDate])=0,[Forms].[frmGetDates].[txtStartDate],([Incident_Tickets].[Cr
eate-date]) Between [Forms].[frmGetDates].[txtStartDate] And
[Forms].[frmGetDates].[txtEndDate])