Date range (1 Viewer)

[lk2827]

so what do you think we should do to fix this? arent us and uk dates based on regioal setting on the PC?

 

[georgedwilkinson]

Sorry, over my head...I can barely figure out US dates. Someone else?

 

[raskew]

lk2827

Please open your query--nothing else-- in SQL view, highlight it, copy it, and then paste it as a response. Do not Zip it!!

Thanks - Bob

 

[Brianwarnock]

I have never, repeat never, had a problem with my UK dates, but I always use a form with the input date fields formatted, the thread pointed at earlier explains this.
I cannot look at Ik's db as it is 2007 which I do not have, and maybe the rules have changed knowing microsoft.

Brian

 

[raskew]

Hi Brian -

Have the same problem re A2007. If lk2827 would be kind enough to post his query SQL, we could perhaps see the problem.

Best Wishes - Bob

 

[lk2827]

SELECT Table1.ID, Table1.Date, Table1.Days, [date]+[days] AS retest
FROM Table1
WHERE (((Table1.Date) Between [first date] And [End date]));

 

[Brianwarnock]

This is absolutely identical to the query on the earlier thread, I will say no more since these kids are absolutely too bone idle to think for themselves.

Brian

 

[georgedwilkinson]

I'm sure somebody probably warned about having a column named "Date", right?

 

[EMP]

lk2827-
Read Jon K's solution on the other thread (the link in post #13 above.)


- Maybe the kids' study text hasn't covered the topic of PARAMETERS Declaration !

^

 

[lk2827]

Thank you for your information every one. I still cants seem to get this working given up on the query and parameters tring it in a form. Can you explain why it still is not working look at the attached db on post 13, as you can see this was made by a member of these fourms therefore I did not do it, but even that will not work???

 

[Jon K]

SELECT Table1.ID, Table1.Date, Table1.Days, [date]+[days] AS retest
FROM Table1
WHERE (((Table1.Date) Between [first date] And [End date]));

If what you wanted is to return the retest dates, the criteria of your query is incorrect. It should be
WHERE (((Table1.Date +Table1.Days) Between [first date] And [End date]))

So even if you have declared the data type of the parameters, it still will not work.


I have attached a sample database in Access 2000 format (I don't have Access 2007.) You can open the form, click on the buttons to run your query (which should produce no results or incorrect results) as well as the query with parameters declaration (which should return the correct results.) The query with parameters declaration is:-

PARAMETERS [first date] DateTime, [End date] DateTime;
SELECT Table1.ID, Table1.Date, Table1.Days, [date]+[days] AS retest
FROM Table1
WHERE (((Table1.Date+Table1.Days) Between [first date] And [End date]));


You can also enter a first date and an end date in the two text boxes on the form and click on the button to run the query "Query based on Form". It should return the correct records (even though no parameters declaration has been made in that query) because the two text boxes on the form for the input dates have been formatted as Short Date.

If you remove the Short Date format from the two text boxes, the query will return incorrect results or no results.

So you can see that to make the query work, you have to tell Access that the two parameters are of Date/Time data type. You can do it by means of parameters declaration in the query or by formatting in the input text boxes on a form.


Note:
Parameters declaration in query Design View is somewhat hidden. You have to choose menu Query, Parameters...
.

 

[lk2827]

Thank you that is what i needed and thanks for explaining it.