If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Criteria where a single date comes from a form
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]) -- Gary O |
#2
|
|||
|
|||
Query Criteria where a single date comes from a form
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 "Gary O" wrote in message ... 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]) -- Gary O |
#3
|
|||
|
|||
Query Criteria where a single date comes from a form
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 "tina" wrote: 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 "Gary O" wrote in message ... 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]) -- Gary O |
#4
|
|||
|
|||
Query Criteria where a single date comes from a form
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 "Gary O" wrote: 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 "tina" wrote: 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 "Gary O" wrote in message ... 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]) -- Gary O |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using current date manipulations for query criteria | Ian AFFS | General Discussion | 6 | April 6th, 2006 05:02 PM |
Make Table Query Using Form Criteria | Tee | Running & Setting Up Queries | 1 | July 26th, 2004 12:13 AM |
set query date criteria to show record 8 days after entry | garry | Running & Setting Up Queries | 1 | July 20th, 2004 01:55 PM |
Date reference as criteria in query | Tim Brown | Running & Setting Up Queries | 1 | June 21st, 2004 01:01 AM |
Recordset in subform based on field in parent form | Lyn | General Discussion | 15 | June 14th, 2004 03:10 PM |