Query Crashes the system

[rmcompute]

The following query keeps crashing my system. It is created programatically
from within the application, where a user can both select individual serial
#s to filter the report on or select them from a table. Is there a way to
properly combine the "In" statement below which reads the table, with an
individual selection?

SELECT qrySAMSHByCodeExternal_Pre01.*
FROM qrySAMSHByCodeExternal_Pre01
WHERE (((qrySAMSHByCodeExternal_Pre01.SerialNum)="H7011100007" Or
(qrySAMSHByCodeExternal_Pre01.SerialNum) In (Select FieldData As SerialNum
From tblSAMImpDD Where FieldName = 'SerialNum')));

 

[Lord Kelvan]

umm your serial number is being treated as a string in the in
statement

Where FieldName = 'SerialNum')));

make it

Where FieldName = [SerialNum])));

 

[Lord Kelvan]

actually scratch that dont use the word serialnum you need to use the
word [fielddata]

Where FieldName = [fielddata])));

or have i got the wrong idea about your in statement

 

[rmcompute]

LK,

Thanks for the response. I simplified the statement to more clearly state
the problem:

SELECT tblSAMHistory .*
FROM tblSAMHistory
WHERE SerialNum = "H7011100007" Or
SerialNum In (Select SerialNum
From tblSAMSerialNum);

This query is created programatically. The user is allowed to select a
serial # on the screen from a dropdown list and then have a text file of
serial #s imported into a table (tblSerialNum). If necessary I will prevent
them from entering the serial # on the screen when using the table. I was
wondering, however, if there is a way to select data from a table using this
combination. I tried parenthesis in different places but it crashed each
time.

WHERE SerialNum = "H7011100007" Or
SerialNum In (Select SerialNum
From tblSAMSerialNum);

For some reason the combination crashes the system.

 

[Lord Kelvan]

how are the users doing this is it from a form if it is then you are
going around it the wrong way

all you need is

SELECT tblSAMHistory .*
FROM tblSAMHistory
WHERE SerialNum = [forms]![formaname]![serialnumcriteria]

 

[rmcompute]

Typically the user selects 1 serial number from a form, but I wanted to give
them an option of entering a group of serial numbers. I decided to not
combine the single selection with the group selection so now the use will
either enter 1 serial number or use the list. This solution is currently
working.

Thanks for the response.