Using Between with Like & *

[wilderfan]

I have a form which feeds a query with a start date and an end date.

In Query design, I use Between to limit the range of records to the requested date range.

If the user chooses NOT to fill in the 2 dates text boxes on the form, can I use Like & * together with my Between criteria in the query design?

I'm not sure what the proper syntax would be. Any suggestions?

 

[DCrake]

Providing the user with two boxes to fill in a date range is what it says. Do not let them continue without a date range.

What you could do is to do a check prior to firing the query

If Me.DateLower = "" Then
   Me.DateLower = #01/01/2000#
End If

If Me.DateUpper = "" Then
   Me.DateUpper = DateAdd("yyyy",5,Date())
End If

Making sure that the lower and upper dates will cover all dates in your table.

 

[sportsguy]

SELECT [Car Hires].Hire_CarID, Cars.Car_Reg, [Car Hires].Hire_Start, [Car Hires].Hire_End, [txthire_start] AS StartRequest, [txthire_end] AS EndRequest
FROM Cars INNER JOIN [Car Hires] ON Cars.CarID = [Car Hires].Hire_CarID
WHERE ((([txthire_start])<=[Hire_End]) AND (([txthire_end])>=[Hire_STart]));

gets your answer. You need to invert your fields and selection criteria dates. .

sportsguy

 

[vbaInet]

You didn't tell us what you want to happen when both boxes are null, but I suspect you want this:

Between [Start Date] And [End Date] Or [COLOR=Blue]([/COLOR][Start Date] Is Null And [End Date] Is Null[COLOR=Blue])[/COLOR]

Set the Format property of the textboxes to a Date Format so only dates are allowed and so that the query doesn't bomb.

 

[wilderfan]

Hi, vbaInet.

If the user does not fill in the 2 date fields, then I'd like the query to produce all records regardless of the date of each record.

 

[vbaInet]

Hi, vbaInet.

If the user does not fill in the 2 date fields, then I'd like the query to produce all records regardless of the date of each record.

Ok, as advised in my post above.

 

[Brianwarnock]

Between [Start Date] And [End Date] Or ([Start Date] Is Null And [End Date] Is Null)

Only works if both are null, what if only one is null?
You could use an OR instead of the And, but perhaps that is considered and error and should be flagged which would require the use of a Form

Brian

 

[vbaInet]

Between [Start Date] And [End Date] Or  ([Start Date] Is Null And [End Date] Is Null)

Only works if both are null, what if only one is null?
You could use an OR instead of the And, but perhaps that is considered and error and should be flagged which would require the use of a Form

Brian

wilderfan is referencing controls on a form already, I think he mentions this in the OP. I just wrote it that way so that the code isn't too long.

It seems he's happy with it showing all records when both fields are Null as expressed here

If the user does not fill in the 2 date fields, then I'd like the query to produce all records regardless of the date of each record.

Perhaps a validation on the form should be put in place to avoid having one field being Null and the other filled in.

 

[Brianwarnock]

vba you are correct, that'l teach me to try to do things in a hurry. apologies all round.

Brian

 

[jbenner]

I had a similar situation. I was trying to use Between, Like and IIf which was obviously not working. Using the Or instead worked brilliantly. Thankyou!