Microsoft SQL syntax

[llafretaw]

Hi,
I'm trying to specify in SQL to be run within VBA code using the DoCmd.RunSQl command to insert into table 1, all the records from table 2 except records where the first field in table 2 is not equal to the column name and where both fields 1 and 2 of a record are blank. Unfortunatley I have tried variants of the following but it only returns a record that has both its first two fields populated and not fields were only one of the two is empty:

WHERE tmpBL.F1 <> 'column name' AND ((tmpBL.F1<> '') AND (tmpBL.F2 <> ''))

also tried:

WHERE tmpBL.F1 <> 'column name' AND (tmpBL.F1 AND tmpBL.F2 <> '')

 

[hennep]

try field is null or field is not null
instead of field='' and field<>''

 

[jebry]

Hi!

Try this:

WHERE tmpBL.F1 <> 'column name' AND (IsNull(tmpBL.F1) = False Or IsNull(tmpBL.F2) = False)

This should cover it unless the Allow Empty Strings property of the fields are set to yes.

hth

Jeff Bridgham
bridgham@purdue.edu

 

[essnrv]

Try this

WHERE tmpBL.F1 <> 'column name' AND (LEN(tmpBL.F1)=0 AND
LEN(tmpBL.F2)=0) Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

 

[llafretaw]

I have tried all of the above but still no go, as some of the fields in table 2 (i.e the table that the values are been selected out of, are not not null, just empty strings) to give a better idea of what I'm trying to do, I have for example 5 records

CASE 1: record one has a value for field_name1 which is equal to 'column_name' hence don't want that record at all in table 2

CASE 2: record 2 has no value for field_name1 or no value for field_name2,(i.e only contain empty strings) so don't want that record either

CASE 3: record 3 has no value for field_name1 but does have a value for field_name2, so would like that record

CASE 4: record 4 has no value for field_name2 but does have a value for field_name1, so would like that record

CASE 5: record 5 has values for both field_name1 & field_name2, so would like that record

tried this type of syntax in access (where clause been the most nb part)
and 2 records are returned that been case 4 & 5, when case 3 should be returned as well....

Insert into table2
select * from table1
where field_name1 <> 'column_name'
and ((field_name1 <> '') or (field_name2 <> ''))

also tried

where field_name1 <> 'column_name'
or ((field_name1 <> '') and (field_name2 <> ''))

as well as

where field_name1 <> 'column_name'
or ((field_name1 <> '') or(field_name2 <> ''))

but can never get the correct three records

any help would be appreciated