Problem with Date Range using Like

[Dmonkyking]

Hi, I've been trying to figure out how to have an Access query display only records with a certain date range, regardless of year. For example, I want the database to list records that have dates between June 1 and June 10, but using a wildcard as a year so it will show all years. Using between apperently doesn't seem to work with wildcards on dates because I get a data type mismatch, unless I'm doing something wrong and I haven't found out how to use Like with date ranges. This is what I have:

SELECT Payment.AmtPaid, Firm.[Firm#], Payment.DatePaid, Payment.[Payment#]
FROM Firm INNER JOIN Payment ON Firm.[Firm#] = Payment.[Firm#]
WHERE (((Firm.[Firm#])="1115") AND ((Payment.DatePaid) Like "6/3/*"))
ORDER BY Payment.DatePaid;

I used "6/3/*" because I was testing the wildcard out. Am I missing something here?

 

[jebry]

Hi!

To let Access know that a literal is a date, enclose it in # symbols instead of quotes. I don't know if you can use the wildcards this way because I have never tried. If you need to, you can create a field in the query just to search on:

SearchDate: Format(Payment.DatePaid,"m/d/yyyy")

Then in the where box put:

Like "6/3/*"

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Dmonkyking]

hey jebry,
Yeah, that was my problem before is if I enclosed in # I couldn't use wildcards, because I guess Access didn't understand them as being wildcards instead if a non date character, which is found invalid. I'm hoping there is a simple way to do this within SQL, outside of doing a Like for each date from June 1 to 10.

 

[jebry]

Hi!

You can use my second suggestion. Here it is in SQL form, what I gave you in the last post was what to put in the query design window:

Select Yourfields From YourTable
Where Format(Payment.DatePaid,"m/d/yyyy") Like "6/3/*"

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[PHV]

list records that have dates between June 1 and June 10
WHERE Month([Payment.DatePaid])=6 AND Day([Payment.DatePaid])<=10 AND Firm.[Firm#])="1115"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Dmonkyking]

Thanks again PHV, worked perfectly. I knew there had to be an easy way to do it.