find specific date for a report

[MikeO]

I need to generate a report that is filtered by a specifc date and a
different report that will serach a date range. The report has certain
criteria defaulted and when opened, it asks the user for a date. regardless
of the date entered, the report is not filtered by the date. I have tried the
common formats such as "like" and "Between" in the criteria field of the
"TransactionDate" field of the query.
Could this be related to how the date is recorded in the history?
Mike

 

[Jeff Boyce]

Mike

If your underlying data (you don't describe this) is being stored as a
date/time data type, you might being finding the difference between looking
for a Date() and looking for a Now(). Before you decide how to search for
the data/range, you need to know how the data is being stored...

(and for us to help, we need to know, too!)

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[MikeO]

The "TransactionDate" is imported from a .csv file that is downloaded from a
barcode scanner. The format for one set of tables is "general date" and the
other (separate database) did not have a format specified. The strange thing
is that I have a form that the user inputs a date range along with other
critera and it appears to filter correctly. I have not yet tried to use a
pop-up form to enter the date for the query.

I appreciate for assistance with this.

Mike O.

 

[John W. Vinson]

The "TransactionDate" is imported from a .csv file that is downloaded from a
barcode scanner. The format for one set of tables is "general date" and the
other (separate database) did not have a format specified. The strange thing
is that I have a form that the user inputs a date range along with other
critera and it appears to filter correctly. I have not yet tried to use a
pop-up form to enter the date for the query.

The format of the field is irrelevant - it merely controls how the field value
is *displayed*, not what's stored or how it's searched.

What's important is the actual *datatype and contents* of the field.

When you imported from the text file was the data put into a Text type field,
or a Date/Time field? Could you post some examples of the actual content of
the field?

A Date/Time value (regardless of format) is stored as a Double Float count of
days and fractions of a day since midnight, December 30, 1899. To do a "fuzzy"
search for a date, you need to do a range - e.g. if you want all results from
November 2008 you would not use

LIKE */11/2008

but instead

BETWEEN #11/1/08# AND #11/30/08#

(assuming that there's no time component in your field).

 

[MikeO]

I tried a few other methods after your replies and I still cannot filter the
report.
I will spend more time with it and if I need to I will post again. I did
check the date field settings. When it is imported from the .csv file it is
saved as short date format. I have a form that is used to select criteria and
display the data. There are 2 reports you can generate, one filters correctly
and the other (using the same criteria) does not. The second query does have
set criteria. I will start removing criteria until it filters correctly and
go from there.
Thank you for your time and expertise. If you are interested, I can send the
reports and tables for evaluation.

 

[John W. Vinson]

When it is imported from the .csv file it is
saved as short date format.

Again:

The format


IS ABSOLUTELY IRRELEVANT


and has nothing to do with the issue.

Please - as requested - post the *data type* of the field.

 

[MikeO]

The data type is Date/Time

Again:

The format


IS ABSOLUTELY IRRELEVANT


and has nothing to do with the issue.

Please - as requested - post the *data type* of the field.

 

[MikeO]

I found the problem.
In the criteria for the query I entered 11 "locations" that I wanted and
exclude all other others. I listed each using "10101" and "10102" and etc...
I did not enter them all on one line. Instead, I put 2 on the first 2 lines
and then one on each line below.
When I removed that criteria, the query filtered by date.
Thank you for your replies.
Mike