Forms and Date Criteria

[mdooley]

Hi



I have a table that includes "course start" and "course finish" dates. I run a variety of reports based around the dates, eg courses running currently, courses that finished last year, courses that start next year, etc etc.

I created one underlying query where I set the required dates. For example, for current courses I'm filtering on two fields - courses with a start date before or equal to today AND a finish date after today and for courses that finished last year I'm only filtering on the finish date and so on.

All well and good, but I'm now trying to set these dates in a form. So a "Current Courses" button will set the Start and Finish dates. Except I can't figure out how to get greater than or less than, etc to pass to the query. If I set a plain date and use the >[Form]![CriteriaForm]![Startdate] that works, but I want to include the ">" in the form, as sometimes the start date will be greater than, sometimes less than and so on. Using me.startdate = ">"& Date() looks correct in the criteria text box, but I get "the expression is typed incorrectly, or it is too complex to be evaluated...." when I run the query.

I hope that makes sense!

Any ideas?

Thanks!

[ranman256]

dates are numeric, dont use quotes around the operand:

where forms!myform!txtstartdate = > Date()

[mdooley]

Thanks for responding.

I'm still doing something wrong. When I click a button for "Current Reports", it populates the date fields on the form, using me.startdate = Date(). This puts the current date in the field and the query runs fine. But when I try to make it a later or earlier date with < or > I get stumped. If I use me.startdate = >Date() I get an error. If I put inverted commas, it appears correctly in the textfield on the form, but the query gives me the error above.

[CJ_London]

but I want to include the ">" in the form, as sometimes the start date will be greater than, sometimes less than and so on. Using me.startdate = ">"& Date() looks correct in the criteria text box, but I get "the expression is typed incorrectly, or it is too complex to be evaluated...." when I run the query.

you can't pass the criteria in that way.

but you can use the eval function to evaluate a string you create and set the criteria =true. something like

SELECT *
FROM myTable
WHERE eval(format(startDate,"yyyymmdd") & forms!myform.txtsign & format(forms!myform.txtStart,"yyyymmdd"))=true

where txtSign is populated with ><=> etc as required and txtStart is a date

important thing is that eval will only evaluate a string, so make sure that is what you have

Alternatively build your entire sql in vba