Using Like[UserDef.]&* on Date Variable in Criteria Field Gives Error?

[JewliaB]

I'm creating a sum query to base my report off of and I am allowing the user to enter the dates it would like the query to run between using the following syntax in the criteria: Between [Enter Start Date:] and [Enter End Date:]. I would like the user to be able to leave the field blank, allowing the following three possibilities:

1. No start date, yes end date yields all results until end date

2. Yes start date, no end date yields all results from start date on

3. No start date, no end date yields all results

So I attempted to use the following syntax in the criteria field:
Between Like[Enter Start Date:]&* and Like[Enter End Date:]&*

However, this gives me an error "The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier."

If it makes a difference, as I said it is a sum query and the total field for this column is set to "Where". Any ideas would be helpful.

 

[MSAccessRookie]

I'm creating a sum query to base my report off of and I am allowing the user to enter the dates it would like the query to run between using the following syntax in the criteria: Between [Enter Start Date:] and [Enter End Date:]. I would like the user to be able to leave the field blank, allowing the following three possibilities:

1. No start date, yes end date yields all results until end date

2. Yes start date, no end date yields all results from start date on

3. No start date, no end date yields all results

So I attempted to use the following syntax in the criteria field:
Between Like[Enter Start Date:]&* and Like[Enter End Date:]&*

However, this gives me an error "The expression you entered contains invalid syntax. You may have entered a comma without a preceding value or identifier."

If it makes a difference, as I said it is a sum query and the total field for this column is set to "Where". Any ideas would be helpful.

In reality, there are only two cases here, with the third overlapping the first two:

1. Start Date Is/Is Not Null
2. End Date Is/Is Not Null

To look at the 4 possibilities arising form these two cases:

1. Start Date Is Null
   • The Start Date needs to default to a Date that is earlier than the first date in the database column being evaluated. To do this, you can use the min() function, or just select an arbitrary date (ex: 7/4/1776), that will always be first.
2. Start Date Is Not Null
   • Use the Start Date as entered
3. End Date Is Null
   • The End Date needs to default to the current Date. To do this, you can use the date() function.
4. End Date Is Not Null
   • Use the End Date as entered

 

[gemma-the-husky]

i would store two public variables, startdate and enddate

now to use everydate from the start, you need to just set the startdate to 0
to use up to all dates is a bit fiddlier - , set enddate to some future date 31/12/2999

then you can just say

between readstartdate and readenddate

-------
you will need function calls to read variable dates in a query - as yuo cant use variable names directly

 

[JewliaB]

MSAccessRookie, I'm not certain how I would go about defining this. I know how to write a basic if function, which it essentially sounds like you're asking me to do with two variables. Essentially, it sounds like I would say (in plain[ish] english) between startdate and enddate where startdate, if left null is 7/4/1776 or min() (or 0, per gemma-the-husky), else entered date and enddate, if left null is date(), else entered date. This is fine, but I have no idea for the syntax for this!! Could someone help me out?

 

[abcman]

Hi There, I had the same problem and if you found the solution then i would appreciate that you tell me how you went around the problem. Thanks a lot.