query

  • Thread starter Thread starter AC
  • Start date Start date
A

AC

I am using the following code in VBA to create a query
based on form comboboxes:
strSQL = "SELECT
tblSamples.RunDate,tblSamples.SampleID,tblSamples.GasFlow,t
blSamples.System_id Where
strSQL = strSQL & " [System_id] like """ & Me!
[txtSystemID] & """"
strSQL = strSQL & " [GasFlow] like """ & Me![txtGasFlow]
& """" etc. with the default value in the form textboxes
set to * but if for example a record in the underlying
table does not have a GasFlow value it is not returned
even though it meets the criteria for System_id. Do I need
some code for Null values and if so how/where?
 
Thanks Chris. I probably didn't phrase my problem clearly
enough. The issue is that if I have 5 combo boxes with
drop down criteria options and I select 20 for GasFlow and
leave all combo boxes set to * my query drops records if
the underlying record does not have a value for one of the
other fields. ie, [GasFlow] is 20 but [SystemId] is blank.
The SQL command is being created through VBA.
-----Original Message-----
Try ...

" AND (([GasFlow] like """ & Me![txtGasFlow] & """) OR
([GasFlow] is null))"

-----Original Message-----
I am using the following code in VBA to create a query
based on form comboboxes:
strSQL = "SELECT
tblSamples.RunDate,tblSamples.SampleID,tblSamples.GasFlow
Click to expand...
,
t
blSamples.System_id Where
strSQL = strSQL & " [System_id] like """ & Me!
[txtSystemID] & """"
strSQL = strSQL & " [GasFlow] like """ & Me! [txtGasFlow]
& """" etc. with the default value in the form textboxes
set to * but if for example a record in the underlying
table does not have a GasFlow value it is not returned
even though it meets the criteria for System_id. Do I need
some code for Null values and if so how/where?



.
Click to expand...
.
Click to expand...
 
Then if you are creating the SQL statement through VBA, DON'T add the criteria
clause in for a field if the corresponding combobox is null.

Since I can't see your VBA code, I can't tell you the exact statement(s) in your
code to change.
Thanks Chris. I probably didn't phrase my problem clearly
enough. The issue is that if I have 5 combo boxes with
drop down criteria options and I select 20 for GasFlow and
leave all combo boxes set to * my query drops records if
the underlying record does not have a value for one of the
other fields. ie, [GasFlow] is 20 but [SystemId] is blank.
The SQL command is being created through VBA.
-----Original Message-----
Try ...

" AND (([GasFlow] like """ & Me![txtGasFlow] & """) OR
([GasFlow] is null))"

-----Original Message-----
I am using the following code in VBA to create a query
based on form comboboxes:
strSQL = "SELECT
tblSamples.RunDate,tblSamples.SampleID,tblSamples.GasFlow
Click to expand...
,
t
blSamples.System_id Where
strSQL = strSQL & " [System_id] like """ & Me!
[txtSystemID] & """"
strSQL = strSQL & " [GasFlow] like """ & Me! [txtGasFlow]
& """" etc. with the default value in the form textboxes
set to * but if for example a record in the underlying
table does not have a GasFlow value it is not returned
even though it meets the criteria for System_id. Do I need
some code for Null values and if so how/where?



.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top