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?

 

[SQLBill]

Is this query using a SQL Server database or an Access database?

If Access, then you need to post in a MS Access forum.

If SQL Server, it won't understand that format. You first need to tell SQL Server how to interpret that date - is it 6 March (dmy) or June 3rd (mdy)? Use SET DATEFORMAT DMY (or MDY) to tell SQL Server how to interpret it.

Then you need to CONVERT the Payment.DatePaid value to the same format.

Or you can use things like DATEPART to compare them.

-SQLBill

Posting advice: FAQ481-4875

 

[Dmonkyking]

Sorry, it would be best to post it in Access forum, because I am using an Access database, thanks for letting me know.