end date

[Trevor3007]

hi,

i am using MSAccess & in the query I use the following;-

Between [Enter Start Date] And [Enter Finish Date] Or Like [Enter Start Date] & "*"

The above runs via a macro & asks for a start date, then end date. The end date will always be whatever 'todays date' is at the time of running the macro'

it wokks ok . but is it possible to change the query so that the end date will always be the relevant 'to days date'? So the user only has to input the desired 'start date'?

many thanks in advance and for your help.

 

[Micron]

Sorry, I tend to be somewhat literal. Comes in handy sometimes though. This is what I meant by a form that I might use. Only thing is, you can't open it from a query but that shouldn't matter because we don't directly play with queries and tables, right?

 

[Trevor3007]

I think you are right, @JonXL.

However, I don't know if it is possible to assign a default value in the internal input box of the query parameter. (I'd love to learn if it is).

Not the best way but maybe the following could be used as the finish date parameter in the query, and instruct users that they can leave it blank to enter today's date.

Nz([Enter Finish Date (press Enter key for today)], Date())

Sorry, I tend to be somewhat literal. Comes in handy sometimes though. This is what I meant by a form that I might use. Only thing is, you can't open it from a query but that shouldn't matter because we don't directly play with queries and tables, right?

many thanks for your reply & help.

I very, very limited access skills. I dont have a scoobie as to where to place ;

Nz([Enter Finish Date (press Enter key for today)], Date())

; in the querie?

i presummed it would replace 'Between [Enter Start Date] And [Enter Finish Date] Or Like [Enter Start Date] & "*"'
So i removed Between [Enter Start Date] And [Enter Finish Date] Or Like [Enter Start Date] & "*" and added

Nz([Enter Finish Date (press Enter key for today)], Date())

but all i got was an error messge stating invalid , or words to that.

This is the SQL from the query:-


SELECT Maintable.Company, Maintable.[Contact Name], Maintable.From, Maintable.To, Maintable.[Contact Email], Maintable.[Contact Number], Maintable.[Company Address], Maintable.[Post Code], Maintable.Role
FROM Maintable
WHERE (((Maintable.From) Between [Enter Start Date] And [Enter Finish Date] Or (Maintable.From) Like [Enter Start Date] & "*"))
ORDER BY Maintable.From DESC;


if you can asist further and provide a step by step , that would very much apprecuited.

thanks again for your help

 

[smozgur]

if you can asist further and provide a step by step , that would very much apprecuited.

No problem. Replace the [Enter Finish Date] part in the original query with the one I suggested. The final SQL string should read as follows (assuming the other parts are working correctly in your query):

SELECT Maintable.Company, Maintable.[Contact Name], Maintable.From, Maintable.To, Maintable.[Contact Email], Maintable.[Contact Number], Maintable.[Company Address], Maintable.[Post Code], Maintable.Role
FROM Maintable
WHERE (((Maintable.From) Between [Enter Start Date] And Nz([Enter Finish Date (press Enter key for today)], Date()) Or (Maintable.From) Like [Enter Start Date] & "*"))
ORDER BY Maintable.From DESC;

 

[smozgur]

The Nz function will evaluate the user entry, and if it is a null value (that means nothing entered as a date but Enter key is pressed) then it will use the second parameter as the entry, which is provided as Date() in the function I suggested.

 

[Trevor3007]

The Nz function will evaluate the user entry, and if it is a null value (that means nothing entered as a date but Enter key is pressed) then it will use the second parameter as the entry, which is provided as Date() in the function I suggested.

wow... that is AMAZING!!!! ?

I replaced the orginal SQL with yours ..and watched the magic.

have a rest of your day.

 

[smozgur]

wow... that is AMAZING!!!! ?

I replaced the orginal SQL with yours ..and watched the magic.

have a rest of your day.

Glad to hear it works!

For your information, I switched the solution post with the one that you confirmed as the solution. That would be great if you could do the same in your future questions as it helps future readers.

 

[Trevor3007]

NP & will do