Date range Q

[markjet]

I am trying to use the date range expression off the MS web site and it not working (Between Date() And Date()-180) I am trying to search for records less then 180 days old. any thoughts

[boblarson]

What is the field's datatype that you are working with and what is the definition of "not working." We can't view what you are seeing so not working to us could mean that it is sitting by the side of the road asking for handouts or it is playing chess in the street. More information would be helpful.

Also, I don't think it makes a difference but you might want to have the earliest date first and then the later

Between Date() -180 And Date()

[markjet]

the data type is dates (1/25/2011) it does not bring up any records and yes I tried switching the dates.

Thanks

[boblarson]

Can you paste the entire SQL string for the query please?

[ketbdnetbp]

Don’t know why the other function is not working.

However, a while back, I used this and it worked for me…

add this: >= DateAdd("d",-180, Now())

to the criteria row of the date field you are trying to filter. See if it returns the records you need.

All the best,


Jim

[markjet]

Here is the SQL:

SELECT tblTestReport.chrProductNumber, tblTestReport.chrProductName, tblTestReport.Report, tblTestReport.[AAMA Cert?], tblTestReport.dtmReportDate
FROM tblTestReport
WHERE (((tblTestReport.dtmReportDate) Between Date() And Date()-180));

[markjet]

thanks Jim but that one does not work either.

[rpeare]

The syntax you have

Between Date() And Date()-180

is correct and valid.

Make sure your DATA TYPE for your date field is DATE/TIME and not text. If it's TEXT you will get nothing back (which seems to be your problem).

[markjet]

thanks for your response rprare but yes it set to DATE/TIME that the first thing I checked.

[rpeare]

Well you can do a couple of things, post a sample db or create a simple table with a date field then create a query on that date field and see if you can get it working for the sample table.

[boblarson]

I've had issues with date/time fields which are storing times with the dates and regional settings interferring. So you could try this:

SELECT tblTestReport.chrProductNumber, tblTestReport.chrProductName, tblTestReport.Report, tblTestReport.[AAMA Cert?], tblTestReport.dtmReportDate
FROM tblTestReport
WHERE (((DateValue(Nz(tblTestReport.dtmReportDate,0))) Between Format(Date(), "\#mm\/dd\/yyyy\#") And Format(Date()-180, "\#mm\/dd\/yyyy\#")));

[markjet]

thanks for the work Bob, but I get the error data type mismatch in criteria expression.

[boblarson]

Do you have nulls in the date field at all?

Also, I think you may be to a point where you will need to upload a copy of your database so we can play with it. It does take some messing with sometimes to get it just right.

[rpeare]

Post your sample db then, every test I've run works with the data I use.