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
|
|||
|
|||
IIF in Query
I have a query that derives its criteria from a form. On the form I have 2
fields: Beginning Date and Ending Date. Here's what I want to accomplish: If the Start date is null, then have it use =End Date; otherwise if the Start date and End date are filled in, I need it to say Between start date and end date. iif(isnull(Forms!frmMyForm![txtStartDate]),tblMyTable!dtmStartDate = Forms!frmMyForm![txtEndDate], tblMyTable!dtmStartDate between Forms!MyForm!txtStartDate and Forms!frmMyForm!txtEndDate) It seems that it doesn't recognize the operators of less than or equal to nor the Between statement. Either I am writing the syntax incorrectly or it cannot be done in an IIF statement. Thanks, D.Collins |
#2
|
|||
|
|||
IIF in Query
D Collins wrote:
I have a query that derives its criteria from a form. On the form I have 2 fields: Beginning Date and Ending Date. Here's what I want to accomplish: If the Start date is null, then have it use =End Date; otherwise if the Start date and End date are filled in, I need it to say Between start date and end date. iif(isnull(Forms!frmMyForm![txtStartDate]),tblMyTable!dtmStartDate = Forms!frmMyForm![txtEndDate], tblMyTable!dtmStartDate between Forms!MyForm!txtStartDate and Forms!frmMyForm!txtEndDate) It seems that it doesn't recognize the operators of less than or equal to nor the Between statement. Son't even try to use Between in this kind of situation. Use = and = instead. I think I would use this kind of criteria: =Nz(Forms!frmMyForm!txtStartDate,#1/1/1000#) AND =Forms!frmMyForm!txtEndDate -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
IIF in Query
Marshall,
Thank you so much. I knew there had to be a way to do this. I really appreciate it. Thanks, D. "Marshall Barton" wrote: D Collins wrote: I have a query that derives its criteria from a form. On the form I have 2 fields: Beginning Date and Ending Date. Here's what I want to accomplish: If the Start date is null, then have it use =End Date; otherwise if the Start date and End date are filled in, I need it to say Between start date and end date. iif(isnull(Forms!frmMyForm![txtStartDate]),tblMyTable!dtmStartDate = Forms!frmMyForm![txtEndDate], tblMyTable!dtmStartDate between Forms!MyForm!txtStartDate and Forms!frmMyForm!txtEndDate) It seems that it doesn't recognize the operators of less than or equal to nor the Between statement. Son't even try to use Between in this kind of situation. Use = and = instead. I think I would use this kind of criteria: =Nz(Forms!frmMyForm!txtStartDate,#1/1/1000#) AND =Forms!frmMyForm!txtEndDate -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|