Query not showing records (1 Viewer)

Bmoore · Aug 5, 2019

Hello All, New to the Access Forum, I'm sure my question has been addressed but i cant seem to find the answer Sorry.... Query not showing records... I have a basic query of 1 table with a between date criteria. I imported the original data from an Excel sheet then I added new fields within Access. I also changed a few data types of a few of the fields. When I run the query using dates within original data dates all is correct, no issues at all. When I run the query using dates of newly added records not a single record shows in the query. Example: All data imported was in the month of March, new records have been added Aug 3,5,8. When searching all of Aug no records shown, when searching the specific day ie. Aug 5 no records shown. In the table they are there, I have refreshed, closed & reopened access but i cant figure out why the records are not being shown. Thanks for any help anyone can give.

theDBguy · Aug 5, 2019

Hi. Welcome to the forum. What is the data type of the field you're trying to filter? How did you enter the new data?

Bmoore · Aug 5, 2019

Attached are some more pics of whats going on...

Bmoore · Aug 5, 2019

theDBguy said:
Hi. Welcome to the forum. What is the data type of the field you're trying to filter? How did you enter the new data?

Im filtering using Between dates in the criteria, new data was entered using a form.

theDBguy · Aug 5, 2019

Bmoore said:
Im filtering using Between dates in the criteria, new data was entered using a form.

Hi. Thanks but I'm still wondering about the data type. Entering something like 4/1 in the criteria prompt might not be resulting in the expected value based on the field's data type.

Bmoore · Aug 5, 2019

theDBguy said:
Hi. Thanks but I'm still wondering about the data type. Entering something like 4/1 in the criteria prompt might not be resulting in the expected value based on the field's data type.

The data type is Date/Time... Pic attached... When using start date 3/1 and end date 3/31 all the records comes back fine.... just when i query dates in Aug

theDBguy · Aug 5, 2019

Bmoore said:
The data type is Date/Time... Pic attached... When using start date 3/1 and end dat 3/31 all the records comes back fine
View attachment 75851

Hi. Can you show us the SQL statement for your query? Just FYI, Date and Time are reserved words, so they are not really good names to use for your fields. Also, if the query doesn't provide any clue, we might have to see the form where you add the new dates.

Bmoore · Aug 5, 2019

The SQL is Access7.jpeg on the 2nd reply

arnelgp · Aug 5, 2019

have you click on Parameter on the design view of your query and set the correct datatype of [start date] and [end date].

theDBguy · Aug 5, 2019

Bmoore said:
The SQL is Access7.jpeg on the 2nd reply

Hi. Sorry, I completely missed that post. Okay, try changing your query to something like this:

Code:

SELECT DISTINCTROW...
FROM DataT
WHERE [Date] Between ...
GROUP BY...
ORDER BY...

Basically, I changed your HAVING clause into a WHERE clause. Hope it helps...

theDBguy · Aug 5, 2019

arnelgp said:
have you click on Parameter on the design view of your query and set the correct datatype of [start date] and [end date].

Thanks, Arnel. That would have been my next suggestion. Cheers!

Gasman · Aug 5, 2019

Wouldn't you still need a full date, not just month and day?

theDBguy · Aug 5, 2019

Gasman said:
Wouldn't you still need a full date, not just month and day?

I believe Access adds the current year to the date, if not provided. Otherwise, the result would be a division, which is why I asked about the datatype.

Gasman · Aug 5, 2019

theDBguy said:
I believe Access adds the current year to the date, if not provided. Otherwise, the result would be a division, which is why I asked about the datatype.

So it does.

Bmoore · Aug 5, 2019

theDBguy said:
Hi. Sorry, I completely missed that post. Okay, try changing your query to something like this:

Code:

SELECT DISTINCTROW... FROM DataT WHERE [Date] Between ... GROUP BY... ORDER BY...

Basically, I changed your HAVING clause into a WHERE clause. Hope it helps...

Im not familiar with writing SQL and little confused at what you mean "From DataT Where [Date].....

Bmoore · Aug 5, 2019

My SQL is
SELECT DISTINCTROW DataT.[Service Advisor], Min(DataT.Date) AS MinOfDate, Sum(IIf([DataT]![Oil & Filiter Change]=Yes,1,0)) AS [Oil Changes], Sum(IIf([DataT]![General Repair]=Yes,1,0)) AS [General Repairs], Sum(IIf([DataT]![General Service]=Yes,1,0)) AS [General Services]
FROM DataT
GROUP BY DataT.[Service Advisor]
HAVING (((Min(DataT.Date)) Between ([Start Date]) And ([End Date])))
ORDER BY DataT.[Service Advisor];
.... Will changing the SQL code require me changing anything else?

theDBguy · Aug 5, 2019

Bmoore said:
Im not familiar with writing SQL and little confused at what you mean "From DataT Where [Date].....

Hi. That was just me being lazy and didn't want to type the whole thing. But basically, if you post your SQL statement, I could copy and paste it. Otherwise, take the image you posted earlier and simply follow the format I laid out. As I said above, the only difference should be the use of the WHERE clause rather than the HAVING clause. So, all you really have to do is fill-in the missing pieces from the your SQL statement, which I didn't type above. Again, I was just being lazy.

theDBguy · Aug 5, 2019

Bmoore said:
My SQL is
SELECT DISTINCTROW DataT.[Service Advisor], Min(DataT.Date) AS MinOfDate, Sum(IIf([DataT]![Oil & Filiter Change]=Yes,1,0)) AS [Oil Changes], Sum(IIf([DataT]![General Repair]=Yes,1,0)) AS [General Repairs], Sum(IIf([DataT]![General Service]=Yes,1,0)) AS [General Services]
FROM DataT
GROUP BY DataT.[Service Advisor]
HAVING (((Min(DataT.Date)) Between ([Start Date]) And ([End Date])))
ORDER BY DataT.[Service Advisor];
.... Will changing the SQL code require me changing anything else?

Hi. Looks like you posted this while I was responding above. Try this one:

Code:

SELECT DISTINCTROW DataT.[Service Advisor], Min(DataT.[Date]) AS  MinOfDate, 
    Sum(IIf([DataT]![Oil & Filiter Change]=Yes,1,0)) AS [Oil  Changes], 
    Sum(IIf([DataT]![General Repair]=Yes,1,0)) AS [General  Repairs], 
    Sum(IIf([DataT]![General Service]=Yes,1,0)) AS [General  Services]
FROM DataT
WHERE  (((Min(DataT.Date)) Between ([Start Date]) And ([End Date])))
GROUP BY DataT.[Service Advisor]
ORDER BY DataT.[Service Advisor];

Hope it helps...

arnelgp · Aug 5, 2019

are you using Text as date field type. becoz' on your posted image it is left align on the datasheet.

Bmoore · Aug 5, 2019

theDBguy said:

Hi. Looks like you posted this while I was responding above. Try this one:

Code:

SELECT DISTINCTROW DataT.[Service Advisor], Min(DataT.[Date]) AS  MinOfDate, 
    Sum(IIf([DataT]![Oil & Filiter Change]=Yes,1,0)) AS [Oil  Changes], 
    Sum(IIf([DataT]![General Repair]=Yes,1,0)) AS [General  Repairs], 
    Sum(IIf([DataT]![General Service]=Yes,1,0)) AS [General  Services]
FROM DataT
WHERE  (((Min(DataT.Date)) Between ([Start Date]) And ([End Date])))
GROUP BY DataT.[Service Advisor]
ORDER BY DataT.[Service Advisor];

Hope it helps...

I copied and pasted it.... This is what its doing

Query not showing records (1 Viewer)

Registered User.

I’m here to help

Registered User.

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

..forever waiting... waiting for jellybean!

I’m here to help

I’m here to help

Enthusiastic Amateur

I’m here to help

Enthusiastic Amateur

Registered User.

Registered User.

I’m here to help

I’m here to help

..forever waiting... waiting for jellybean!

Registered User.

Similar threads

Users who are viewing this thread