Create a report from a query (1 Viewer)

Mick3911

Registered User.
Local time
Today, 13:23
Joined
Jul 31, 2018
Messages
40
Hi,

I am trying to open a report that lists all records from a query for a Supplier between 2 dates using a form to input the required parameters.

The 3 parameters are;
Start Date
End Date
Supplier

These are on the form as unbound fields.

In the query I have the following criteria;
Date; Between [Forms]![frmSearchSupp]![txtStartDate] And [Forms]![frmSearchSupp]![txtEndDate]

Supplier; [Forms]![frmSearchSupp]![txtSupplier]

The report opens OK if I just use the date criteria but it displays no records at all when I use the Supplier criteria as well.

Would appreciate any guidance on this.

Thanks Mick
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:23
Joined
May 21, 2018
Messages
8,463
Can you post the actual SQL for the query?
 

Ranman256

Well-known member
Local time
Today, 09:23
Joined
Apr 9, 2015
Messages
4,339
It should if all 3 params are exact.
 

Mick3911

Registered User.
Local time
Today, 13:23
Joined
Jul 31, 2018
Messages
40
I didn't use SQL, I used Between [Forms]![frmSearchSupp]![txtStartDate] And [Forms]![frmSearchSupp]![txtEndDate] and [Forms]![frmSearchSupp]![txtSupplier] in the criteria fields of the query.
 

Mick3911

Registered User.
Local time
Today, 13:23
Joined
Jul 31, 2018
Messages
40
I didn't use SQL, I used Between [Forms]![frmSearchSupp]![txtStartDate] And [Forms]![frmSearchSupp]![txtEndDate] and [Forms]![frmSearchSupp]![txtSupplier] in the criteria fields of the query.

I have found this if this of any help;

SELECT tblNCRDetails1.[Report No:], tblNCRDetails1.[Internal NCR No:], tblNCRDetails1.[Date:], tblNCRDetails1.[Supplier:], tblNCRDetails1.[Compliant Details:]
FROM tblNCRDetails1
WHERE (((tblNCRDetails1.[Date:]) Between [Forms]![frmSearchSupp]![txtStartDate] And [Forms]![frmSearchSupp]![txtEndDate]));
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:23
Joined
May 21, 2018
Messages
8,463
SQL needs to have dates in us format "mm/dd/yyyy". This is not an issue if the textboxes were bound, because it can handle a "real" date value and convert it. I would try two things to test

1) in your textboxes type the dates like #mm/dd/yyyy# and see if that works
2) Try in your query to put a format on the dates
Format(cdate([Forms]![frmSearchSupp]![txtStartDate]),""\#MM/DD/YYYY\#")
Number 2 will convert the text box variant value into a date and then format it correctly. That way if you type in using your regional format it should work.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:23
Joined
Sep 21, 2011
Messages
14,041
UK date format also works for unbound textboxes?
 

Mick3911

Registered User.
Local time
Today, 13:23
Joined
Jul 31, 2018
Messages
40
The text boxes for the dates are formatted as 'Medium Date' and dates selected using date picker or typed in. The report opens fine when using just the dates. There seems to be a conflict when I amended the query to use the Supplier criteria as well as the date criteria.
 

isladogs

MVP / VIP
Local time
Today, 13:23
Joined
Jan 14, 2017
Messages
18,186
Do you really have several fields with a colon : at the end of the field name?
That could cause all sorts of issues.
 

isladogs

MVP / VIP
Local time
Today, 13:23
Joined
Jan 14, 2017
Messages
18,186
Good. Never use special characters or spaces in object names or field names.
I avoid the underscore as well but that's personal preference.
Use CamelCase to join two words in a name

I answered a question yesterday where the OP had a field name consisting of one character - a ?
The database had got corrupted. What a surprise!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:23
Joined
May 21, 2018
Messages
8,463
There seems to be a conflict when I amended the query to use the Supplier criteria as well as the date criteria.
Any chance you are querying on the supplier ID instead of the supplier name field? You do not show the supplier in the query?

If you remove the colons, you should also remove all the spaces. These are also bad. But if you remove the colons rename this field "Date:" because that will then become a reserved word (names of access/sql/vba objects methods).
 

Mick3911

Registered User.
Local time
Today, 13:23
Joined
Jul 31, 2018
Messages
40
Have deleted the colons and still get the same problem.

My naming conventions have improved as I have go on with this database. (Honest!)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:23
Joined
May 21, 2018
Messages
8,463
can you post the real query with the supplier parameter?
 

Mick3911

Registered User.
Local time
Today, 13:23
Joined
Jul 31, 2018
Messages
40
Hi,

Apologies for not replying sooner.

I ended up rebuilding the necessary parts again and it all works now.

Thanks all for your input.
 

Users who are viewing this thread

Top Bottom