Query misfiring and giving wrong results

[unclemeat]

hi bob
this is the screenshot of the query i was using. i used a date of 28/03/2023.
in the results i got a number of results showing dates before that.
i get 283 results - both if i open the reports f form and put that date in, OR if i dont and just add 28/03/2023 as a parameter when asked.


the correct result is 105 results, which you get if i revert to my original >[Forms]![reports f]![date] formula in the query (NB : you ONLY get this result entering the date as a parameter if form reports f is not open. if reports f is open you get the 283 results.

hi Bob
sorry im not ignoring you - i was just trying to get the tables you mentined put in etc. i did read the pge you suggested as well as watched a couple of richard rost youytube vids.

the query did work, it just gives the wrong set of results - ill take a screen shot of the query for you shortly.
appreciating the help.
thx
rob

[Welshgasman]

OK, can we have the latest DB then?
As the one I have has a timestamp in RecordCreated for shopify, so will NEVER match just a date?
Issue Date is even a text field?????

How many are you meant to get in shopify for 6th March 2023?

[Welshgasman]

OK, got to the bottom of it. [Phew:]

In the form you have a default value for date (stupid name btw, as it is a reserved name) as ='01/01/2022'
If you click the calendar icon on the form, it opens to that date. Even when you put 01/04/2023 in the form, the value still comes up as 01/01/2022. Tested that in the immediate window and then removed the default and tested again.

Once that default was removed, the query behaves as it should. Well in fact it has always been behaving as it should, it was just getting the incorrect data to work with.

[unclemeat]

thank you - i will change the name of the default value and leave it blank.
lots of queries to alter too.
cheers - ill be back with questions about my normalising im sure.

OK, got to the bottom of it. [Phew:]

In the form you have a default value for date (stupid name btw, as it is a reserved name) as ='01/01/2022'
If you click the calendar icon on the form, it opens to that date. Even when you put 01/04/2023 in the form, the value still comes up as 01/01/2022. Tested that in the immediate window and then removed the default and tested again.

Once that default was removed, the query behaves as it should. Well in fact it has always been behaving as it should, it was just getting the incorrect data to work with.

[unclemeat]

hi welshgasman
is there any way of getting queries to look up a field like that when i input the date. i have a macro that runs through about 5 queries all of which are looking for a date - i can input them when the parameter box comes up but it would be preferable if i only had to do it once and all the queries could look somewhere for the date.
ta
rob

[Welshgasman]

Just refer to the form as you were doing. No need for any formatting, just enter the date as normal.

Edit: Form does need to be open for that to work though.