Problem with Select query using values from form 1

[48Highlander]

I have a select query as follows:

SELECT tblSponsorships.SponsorID, tblSponsorships.Amount, tblSponsorships.TypeCode, tblSponsorships.Date
FROM tblSponsorships
WHERE (((tblSponsorships.TypeCode)=[Forms]![frmReportSelector].[cboTypeSelect]) AND ((tblSponsorships.Date)>=[forms]![frmReportSelector].[txtStartDate] And (tblSponsorships.Date)<=[forms]![frmReportSelector].[txtEndDate]))
ORDER BY tblSponsorships.SponsorID;

The control cboTypeSelect can have values from 1 to 6 plus *. The asterisk is added to the combo rowsource of frmReportSelector programatically and represents "all" of the types.

Whenever I select <all> types using the combobox, no records are selected. The query works fin if the value of the combobox is a value of 1 through 6. How can I get this query to return records for all types within the date range specified?

 

[PHV]

Have you tried the Like operator instead of = ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[48Highlander]

I have tried valuing cboTypeSelect as LIKE "*" and LIKE * as well as LIKE "%" and LIKE %.

The message I get is "The expression is typed incorrectly or is too complicated to be evaluated."

 

[mordja]

Are the fields date fields, have you tried using <= "#" &[forms]![frmReportSelector].[txtEndDate] & "#"

Mordja

 

[PHV]

What about this ?
SELECT S.SponsorID, S.Amount, S.TypeCode, S.Date
FROM tblSponsorships S
WHERE S.TypeCode Like [Forms]![frmReportSelector].[cboTypeSelect] AND S.Date Between [Forms]![frmReportSelector].[txtStartDate] And [Forms]![frmReportSelector].[txtEndDate]
ORDER BY S.SponsorID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[48Highlander]

The problem is not with the date fields since the query work properly when the value of cboTypeSelect is an integer. So the date filed evaluation is working properly.

The error only occurs when cboTypeSelect is some value other than an integer. (tblSponsorships.TypeCode contains integers taht range ffrom 1 to 6 and the filed cannot contain blanks.)

 

[PHV]

Another way:
SELECT S.SponsorID, S.Amount, S.TypeCode, S.Date
FROM tblSponsorships S
WHERE (S.TypeCode=[Forms]![frmReportSelector].[cboTypeSelect] Or [Forms]![frmReportSelector].[cboTypeSelect]='*') AND S.Date Between [Forms]![frmReportSelector].[txtStartDate] And [Forms]![frmReportSelector].[txtEndDate]
ORDER BY S.SponsorID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[48Highlander]

Thanks PH. That was a BFO (blinding flash of the obvious)!

 

[Latenights]

PHV - An extension of this problem is causing me some heartburn. Same siutation, cbo field on a form feeding the criteria for a field in the query just like 48Highlander (although a text field rather than numeric).

BUT .... IF the user selects the "*" from the cbolist on the form for ALL items, THEN I need to have the query find

WHERE FIELD_X IS LIKE "*" OR ISNULL since some records have values and others are null. Any way to handle this without building and executing my own sql string from within VBA code?

Similarly, stuffing a form variable with the correct string for the qry criteria 'Like "*" or isnull' yields no records found while typing the same string value into the qry manually finds the correct records.

I believe the query actually translates the cbo variable into (Like 'like "*" or is null') so it fails.

 

[PHV]

What about something like this ?
WHERE Nz(Field_X) Like [Forms]![yourForm]![yourCombo]

with the bounded column of your combo returning * for All.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244