Perform a BETWEEN 2 dates from a form query

[j.smith1981]

I am not sure if I have phrased the title properly here.

But I have 2 text fields.

What I was wanting was some idea of how to get a database query working where it looks between 2 dates, all I require is the SQL syntax and if you can note what it relates to, that would be easier for me to convert to my own.

Any help is greatly appreciated.

Thanks,
Jeremy.

 

[Brianwarnock]

If the dates are in Text format you will need to convert to date format before you can work with them.

Brian

 

[j.smith1981]

I have an input mask of the same format as the date in the table that holds all data is this what your reffering to?

 

[Brianwarnock]

With you mentioning 2 text fields I assumed that you had 2 date fields in text not date/time format. I think that more info on your data and what you want is required, this could be very simple or very messy.

Brian

 

[j.smith1981]

Its already in the short date format.

Just a general rule of what to put in would be helpful, this is what I have so far minus the 2 date fields:

SELECT data.test_date, tech.id, tech.tech_name
FROM tech INNER JOIN data ON tech.id = data.tech_id
WHERE (((tech.id)=[Forms]![query_form]![cmb_tech_name]));

I mean logically speaking (not sure if this would work even on its own basis would be something like):

BETWEEN (((data.test_date)=[Forms]![query_form]![from_date_txt] AND [Forms]![query_form]![to_date_txt]

Where 'data' is the table name, test_date is the field I want the query to look between and from_date_txt and to_date_txt is the field inputs masked to date format and short date format regarding properties.

It should all work but I need help just integrating the between query into the where clause.

This is what I have attempted but its refusing to even accept it, probably something to do with my syntax, I am better now with PHP and MySQL than I have anything to do with Microsoft.

Here's it:

SELECT data.test_date, tech.id, tech.tech_name
FROM tech INNER JOIN data ON tech.id = data.tech_id
WHERE (((data.test_date) BETWEEN [Forms]![query_form]![from_date_txt]) AND [Forms]![query_form]![to_date_txt]) AND ((tech.id)=[Forms]![query_form]![cmb_tech_id]));

 

[j.smith1981]

Ok I have it working now, I wont copy in the details of the Query designer, just the actual SQL, here it is:

SELECT data.test_date, tech.id, tech.tech_name

FROM tech INNER JOIN data ON tech.id = data.tech_id

WHERE (((data.test_date) Between [Forms]![query_form]![from_date_txt]
And [Forms]![query_form]![to_date_txt])
AND ((tech.id)=[Forms]![query_form]![cmb_tech_id]));

The above SQL works now, but you have to enter a value into the 2 date fields '[from_date_txt]' and '[to_date_txt]'

But what if the user hasnt entered any date in either date fields, is it then possible to maybe get Access to remove the line that selects only data within that date range when the user hasnt entered a date?

How would I achieve this?

Thanks for your help so far, been a real eye opener getting quite good with Access now, thanks to this forum!

Much appreciated and I look forward to your reply,
Jeremy.

 

[JamesMcS]

Are you using a command button to execute the query with the date criteria? You could put something in the on click property of the button to say "If from_date or to_date is null, me.filteron=false, if not, me.filter= date is between from_date and to_date". This will show all the records if the user doesn't enter a date in either box. (If not a command button, use the afterupdate property of the text boxes)

 

[j.smith1981]

Are you using a command button to execute the query with the date criteria?

I am yes.

Will have a bash at trying that then.

 

[Brianwarnock]

Ok I totally misunderstood your initial question.

Take a look at this thread.


http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Brian

 

[j.smith1981]

Would I enter the code into a macro in the Visual Basic Editor or that pesky menu thing?

Its just its set to that menu thing, with the action OpenQuery with arguements: form_query, Datasheet, Edit

How would I make it so that it runs it without the date range from here?

I dont like programming in Access find it confusing and its more basic to what I usually code in.

Any help though is greatly appreciated,
Jeremy.

Woops sorry I didnt see your reply above, that looks lots more promising than what I was thinking.

Will try that then the link you posted above, sorry about that just overlooked your reply, must not be so awake today haha.

 

[j.smith1981]

Ok I totally misunderstood your initial question.

Take a look at this thread.


http://www.access-programmers.co.uk/forums/showthread.php?t=103312

Brian

The guidelines in the link you posted above worked, thanks ever so much!

 

[Brianwarnock]

Glad we got it sorted , sorry about the original misunderstanding.

Brian

 

[j.smith1981]

I am getting really confused as to why this isnt working.

I have it working so when the user cant enter a date range but is it possible to get this working:

SELECT data.test_date, tech.id, tech.tech_name

FROM tech INNER JOIN data ON tech.id = data.tech_id

WHERE (((data.test_date) Between [Forms]![query_form]![from_date_txt] And [Forms]![query_form]![to_date_txt]) AND ((tech.id)=[Forms]![query_form]![cmb_tech_id])) OR (((tech.id)=[Forms]![query_form]![cmb_tech_id]) AND (([Forms]![query_form]![from_date_txt]) Is Null)) OR (((tech.id)=[Forms]![query_form]![cmb_tech_id]) AND (([Forms]![query_form]![to_date_txt]) Is Null)) OR ((([Forms]![query_form]![to_date_txt]) Is Null) AND (([Forms]![query_form]![cmb_tech_id]) Is Null));

So that the same happens if a '[Forms]![query_form]![cmb_tech_id]' isnt selected?

cmb_tech_id is a combo box on the form.

This is the one that works with the empty date range:

SELECT data.test_date, tech.id, tech.tech_name

FROM tech INNER JOIN data ON tech.id = data.tech_id

WHERE (((data.test_date) Between [Forms]![query_form]![from_date_txt] And [Forms]![query_form]![to_date_txt]) AND ((tech.id)=[Forms]![query_form]![cmb_tech_id])) OR (((tech.id)=[Forms]![query_form]![cmb_tech_id]) AND (([Forms]![query_form]![from_date_txt]) Is Null)) OR (((tech.id)=[Forms]![query_form]![cmb_tech_id]) AND (([Forms]![query_form]![to_date_txt]) Is Null)) OR ((([Forms]![query_form]![to_date_txt]) Is Null));

As I said before, it wont allow for an empty '[Forms]![query_form]![cmb_tech_id]' field.

Thats just what Access has written when you right click on criteria, not the best way to show any type of SQL, its annoying is Access!

 

[Brianwarnock]

This is untested off the top of my head biut I think the where clause would be

WHERE ((data.test_date Between [Forms]![query_form]![from_date_txt] And [Forms]![query_form]![to_date_txt]) Or ([Forms]![query_form]![from_date_txt]) Is Null))
AND (tech.id=[Forms]![query_form]![cmb_tech_id] OR [Forms]![query_form]![cmb_tech_id] Is Null)


Brian

 

[j.smith1981]

This is untested off the top of my head biut I think the where clause would be

WHERE ((data.test_date Between [Forms]![query_form]![from_date_txt] And [Forms]![query_form]![to_date_txt]) Or ([Forms]![query_form]![from_date_txt]) Is Null))
AND (tech.id=[Forms]![query_form]![cmb_tech_id] OR [Forms]![query_form]![cmb_tech_id] Is Null)


Brian

Thats wonderful it worked!

I think its just a Monday, thats my excuse, looking at SQL on a Mondays not good for me, but thank you soo much for your help.

Really appreciate it!

 

[j.smith1981]

Sorry to trouble you again, just a bit stuck.

I am trying to validate this form against the idiot user if you like.

But is there anyway of making it so that, if the user inputs say 01/01/2009 in the from date field, and then enters a lower value in the to date field, like something daft like 01/01/2007 (just as a random example).

Is there anyway of preventing the user from actually performing the query if the above data was inputted please?

Thanks again,
Jeremy.

 

[boblarson]

In the before update event of the TO text box, use this code:

If Me.ToTextBoxNameHere < Me.FromTextBoxNameHere Then
   Cancel = True
   Me.ToTextBoxNameHere.Undo
   Msgbox "You can't have a 'TO' date that is earlier than the 'FROM' date.", vbExclamation, "Error"
End If

 

[jonno_g]

Bob,

I'm using the code you've given above to check that the TO date is later than the FROM date, and it works fine but I'd like to empty the field and then return focus to it so the user can either enter an acceptable date, or no date at all.

At the moment it returns and highlights the incorrect (earlier) date, but the user can then click any other field on the form and it leaves the incorrect date in the field without giving the error message to the user this time.

All of my attemps at this so far have failed - any suggestions?

 

[boblarson]

Bob,

I'm using the code you've given above to check that the TO date is later than the FROM date, and it works fine but I'd like to empty the field and then return focus to it so the user can either enter an acceptable date, or no date at all.

At the moment it returns and highlights the incorrect (earlier) date, but the user can then click any other field on the form and it leaves the incorrect date in the field without giving the error message to the user this time.

All of my attemps at this so far have failed - any suggestions?

You put this in the BEFORE UPDATE event of the TO text box? Post the EXACT code you are using (including the sub header/footer).

 

[jonno_g]

You put this in the BEFORE UPDATE event of the TO text box? Post the EXACT code you are using (including the sub header/footer).

Yes, I've used it exactly as you suggested...

Private Sub txtDateTo_BeforeUpdate(Cancel As Integer)
    If Me.txtDateTo.Value < Me.txtDateFrom.Value Then
        Cancel = True
        Me.txtDateTo.Undo
        MsgBox "A date range must END later than it BEGINS!", vbCritical
    Else
    End If
 
End Sub