Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

query returning all values

Forum home » Delegate support and help forum » Microsoft Access Training and help » Query returning all values if combo box null

Query returning all values if combo box null

ResolvedVersion 2010

Frederick has attended:
Excel Intermediate course
Excel Advanced course
Access Intermediate course
Excel VBA Introduction course
Excel VBA Intermediate course

Query returning all values if combo box null

I am currently using a query that checks values selected in 3 separate combo boxes with the fields in the table, if they match up, the query returns matching results (where all 3 fields line up).

However, I wish to change the queries such that if a field is left blank in the combo box on the form, for that part of the query, all results are returned. eg. if there is no user value in the combo box, all users are returned subject to the alignment in the other two queries.

The 3 fields that are currently used in the query are:

1) User (User_check in the combo box)
2) Product (product in the combo box)
3) Trade Type (TradeType in the combo box)

Here is the code in SQL as it stands:

SELECT tblBusted.Date, tblBusted.Time, tblBusted.DealId, tblBusted.TradeType, tblSupervisors.User, tblBusted.Product, tblBusted.Date, tblBusted.Date
FROM tblSupervisors INNER JOIN tblBusted ON tblSupervisors.User = tblBusted.User
GROUP BY tblBusted.Time, tblBusted.DealId, tblBusted.TradeType, tblSupervisors.User, tblBusted.Product, tblBusted.Date, tblBusted.MarketId, tblBusted.Strip, tblBusted.BuyerName, tblBusted.BuyerCompany, tblBusted.SellerName, tblBusted.SellerCompany, tblBusted.Date
HAVING (((tblBusted.TradeType)=Forms!frmQueryState!TradeType) And ((tblSupervisors.User)=Forms!frmQueryState!User_check) And ((tblBusted.Product)=Forms!frmQueryState!product) And ((tblBusted.Date) Between Forms!frmQueryState!Fromdate And Forms!frmQueryState!Todate));


Any help would be appreciated.

Regards,

Frederick

RE: Query returning all values if combo box null

Hello Frederick,

Hope you enjoyed your Microsoft Excel and Access courses with BEST Training.
Thank you for your question regarding Access and SQL.

Your question is more complex than the forum caters for but if you would like us to take a deeper look, we would need to see your database and there may be time and costs invovled to come up with a solution.

I have asked my colleage to contact you regarding this.

Kind regards,

Richard

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Fri 27 May 2016: Automatically marked as resolved.

Access tip:

Prefixed Autonumbering

When using Date Type = Autonumber as a primary key, clients may require autonumbering to be prefixed with a letter.

For example, P001, where P is the prefixed letter

Instructions
Step 1. Create Field Name
Step 2. Set Data Type to Autonumber
Step 3. Within Field Property General tabsheet,
enter Format property as [backslash]P000



View all Access hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.