Between Dates inconsistency (1 Viewer)

[Allan]

I have an Access 2013 database containing a number of unique ‘Clients’ and each client has multiple events attached including the date of the event (actually it is the date the information was received). Currently there are 4000+ events for 100+ clients.

I am having difficulty with a query in producing a list of events for a single client between two dates, the client is selected from a listbox on a form.
My problem is that the query will not include any data from the current year.

In the query’s field for Client criteria I have:
[forms]![frmMain]![List3]

To isolate the records for the client highlighted in the listbox, in the field DateReceived criteria I have:
Between [Forms]![frmMain]![FirstDate] And [Forms]![frmMain]![SecondDate]

As long as I have a date prior to 1 Jan 2015 in the ‘firstdate’, the query will produce all events for the selected client before 1 Jan 15 (none for the current year).

I have tried several ‘combinations’ including using < and > but cannot understand why the events from 2015 are not listed. The form containing the listbox has the two unbound text boxes (Date Fields) [FirstDate] and [SecondDate] and has an entry in each when I run the query.

Thanks
Allan

 

[arnelgp]

use Format function in your query:

Between #" & Format([Forms]![frmMain]![FirstDate],"mm\/dd\/yyyy") & "# And #" & Format([Forms]![frmMain]![SecondDate],"mm\/dd\/yyyy") & "#"

 

[Allan]

Thanks ArnelGP

Your formula gives a format error. However one of the "combinations" I had previously tried was:
Between Format([Forms]![frmMain]![FirstDate],"dd/mm/yyyy") And Format([Forms]![frmMain]![SecondDate],"dd/mm/yyyy")

If I have the first date as 1 Jan 2015, the result is blank. If I change the date to 1 Jan 2014, I will get all of 2014 records and not records from 2015.

The puzzling part is simply changing the date to pre 2015 will produce results??

 

[arnelgp]

does the field your querying include time stamp?

 

[arnelgp]

>= [Forms]![frmMain]![FirstDate] And <= [Forms]![frmMain]![SecondDate]

 

[Allan]

The field data source is a table in a split database (linked) and the DateReceived is manually inserted into a 'MediumDate' field on a form.

I tried your second suggestion, albeit I have used >< without the = sign.

Both work, both produce the 2014 records but 2015 stays in the ether.

I have set up temporary query using the table as a direct source and it duplicates the problem in my database query.

In another query I call for records over the last six months with:
Between Date()-182 And Date()

I changed 182 to 365 in the other query and produce 2014 and 2015 records, I tried it in the problem query and only got the 2014 records. At least I have another avenue now to explore. Why the old query works and not the new.

I deleted the reference to the list box, and while all records for all clients populated the query it DID include 2015. I then nominated a client by name in the criteria field of the query (eg: "Client XYZ') and the 2015 records were dropped. I cannot work out why the list box or nominating a specific client would change the data set.

 

[arnelgp]

i guess you have to examine the table again, specially the date field, what is the format, short date, long date, date with time, etc.

 

[Allan]

I have checked all the fields for consistency with no apparent problems so I have tried to duplicate the problem in a new database. I imported some data from Northwind and set up a similar form and query.

The problem may not be a 'Date' issue. I note in the query that if I removed the Forms!frmmain!list8 criteria, the between dates will list the relevant data. But will not return any data if I nominate a 'customer' in the listbox.

Can anybody advise if I have properties set incorrectly please.

Thanks
Allan

 

[JHB]

..
The problem may not be a 'Date' issue. I note in the query that if I removed the Forms!frmmain!list8 criteria, the between dates will list the relevant data. But will not return any data if I nominate a 'customer' in the listbox.

Because you've no link between the company and the client.
You can't tell which company a customer is from.

 

[arnelgp]

i told you to check for consistency, i've found out that your Clients table, has clientname like 'Customer A', 'Customer B', etc, while your CustomerOrders table has Company field with values 'Company A', 'Company B'. did you see the difference 'Customer' and 'Company', no wonder no data is being returned by your query, there are no problem with the dates criteria.

 

[gemma-the-husky]

Between [Forms]![frmMain]![FirstDate] And [Forms]![frmMain]![SecondDate]

As long as I have a date prior to 1 Jan 2015 in the ‘firstdate’, the query will produce all events for the selected client before 1 Jan 15 (none for the current year).

I have tried several ‘combinations’ including using < and > but cannot understand why the events from 2015 are not listed. The form containing the listbox has the two unbound text boxes (Date Fields) [FirstDate] and [SecondDate] and has an entry in each when I run the query.

you haven't actually said what dates you were using.

What values do you have for firstdate and secondate?

 

[obeylele]

Based on your initial post I believe there is an issue with date format.
try using this

Between Format([Forms]![frmMain]![FirstDate],"m/d/yyyy") And Format([Forms]![frmMain]![SecondDate],"m/d/yyyy")

as for the sample database you posted as other have posted there are number of issues.

I am attaching 'fixed' version, too lazy to point out the difference hopefully you will figure out.

 

[spikepl]

Too many people start suggesting wrong solutions (and do not read prior posts, since the cause has been found and was sloppy data management).

No you do not need to convert a date control's contents to a string formatted according to mm/dd/yyyy. When you use fully qualified controls in SQL you just leave them be - the system knows that a date is a date, and its numerical value is entirely uninfluenced by format.

Therefore the suggestions in #2 and #12 do not harm but are redundant.

 

[Allan]

Apologies. That is embarrassing, it does not pay to rush an example before having to go out.

However my original problem remains.

Dave
I have used various dates and the result will only produce records prior to 31 Dec 14. When the ‘firstdate’ is I Jan 2015 or later, no records are returned. My ‘firstdate’ test value is 1 Jan 14 and I change the second date between 31 Dec 14 and any date in 2015 because I know there are 35 relevant records in 2014 and that figure will consistently be returned. I use 1 Jan 14 for the known total because the results are in a report detailing statistical information. When I noticed the missing 2015 records, I worked backwards to just running a query and could see in the results both the total and the dates.

Obeylele
Tried your suggestion. It works but still produces only pre 2015 records.

Spikepl
Thanks. Although I tried several changes to the date format, I was never confident that was the cause of the problem because results were always produced with the original, albeit missing a particular year. (Between [Forms]![frmMain]![FirstDate] And [Forms]![frmMain]![SecondDate])

Thank you everyone for your suggestions. Searching this forum and others, had not given me a clue why some records were being isolated and from responses received, it appears the problem is not common. I apologise for confusing the issue by attaching a sloppy example. I will try and duplicate the situation in another database, perhaps starting again may identify where the problem is. I will post any results.

 

[arnelgp]

as i have mentioned, the problem is not on the date criteria its on your listbox, rowsource showing "Customer A", etc., while you want to find it in a field (Company) in table CustomerOrders which has values like "Company A", "Company B", etc.

 

[obeylele]

I note in the query that if I removed the Forms!frmmain!list8 criteria, the between dates will list the relevant data.

Based on that it is probably issue with your data. can you check equivalent filed in your original db to [Company] field from CustomerOrders table.

 

[gemma-the-husky]

allan

from post#1

In the query’s field for Client criteria I have:
[forms]![frmMain]!
[List3]

To isolate the records for the client highlighted in the listbox, in the field DateReceived criteria I have:
Between [Forms]![frmMain]![FirstDate] And [Forms]![frmMain]![SecondDate]

As long as I have a date prior to 1 Jan 2015 in the ‘firstdate’, the query will produce all events for the selected client before 1 Jan 15 (none for the current year).

your settings look correct without any amendment, which is why I asked exactly what dates you were using.

Try a copy of the query with the values for the client and the dates hard-coded in the query. If you get a difference then there is something wrong with the form, I would think.

arnelgp has suggested this already.

 

[Allan]

Re: [SOLVED] Between Dates inconsistency

Time to eat humble pie again and apologise for wasting everyone’s time.

I set up a new database from scratch, importing the tables etc as I did the first time and the code worked first try.

After comparing the new with my previous attempt, I noticed the new had an additional 19 records. Long story short - from the 2458 “events” recorded against 100+ clients, the one client (XYZ) I was selecting to test had all of this year’s events missing (19). When I was running the query without the listbox selection, I was getting all the records for all clients including those from 2015, what I did not notice was that XYZ did not have any events for this year, I knew they did have records in the actual live database and a fair sample range, that was why I was using them in the listbox.

I suppose my new problem is how did the importing of the table in the first instance, dropped nineteen 2015 records for just one client. I can assure you I do not intend to try and work that out nor put it on this forum. For anyone interested and who have read this far – I was setting up a report to display the percentages of ‘events’ (divided into seven event categories) for any client over any given period of time.

Again, I am sorry for wasting your time.