Select Statement

[Guest]

Hi All,

Could you please advise is there any difference between text & numeric
fields in select statement, in below statement it is working perfect "Mloc"
which is character field but when I change the type of field it doesn't work:

sqltxt = "insert into reports.MSS_Analysis_Master select * from " & _
" " & mYear & " where entry_date >='" & Me.DtFrom & "' And
entry_date <='" & Me.DtTo & "' and ShLoc ='" & MLoc & "'"

Thanks for your help in advance
AA

 

[Douglas J. Steele]

Text fields need quotes as delimiters, numeric fields don't (and dates
require # delimiters, plus must be in a format that Access will recognize,
which is independent of what the Short Date format has been set to through
Regional Settings)

If ShLoc is numeric, change

" and ShLoc ='" & MLoc & "'"

to

" and ShLoc =" & MLoc

I'm assuming that entry_date is a text field. If it's a date field, you
should be using

sqltxt = "insert into reports.MSS_Analysis_Master select * from " & _
mYear & " where entry_date >=" & _
Format(Me.DtFrom, "\#yyyy\-mm\-dd\#) & " And entry_date <=" & _
Format(Me.DtTo, "\#yyyy\-mm\-dd\#") & _
" and ShLoc =" & MLoc

 

[Guest]

Thank you so much for your help sir!

Text fields need quotes as delimiters, numeric fields don't (and dates
require # delimiters, plus must be in a format that Access will recognize,
which is independent of what the Short Date format has been set to through
Regional Settings)

If ShLoc is numeric, change

" and ShLoc ='" & MLoc & "'"

to

" and ShLoc =" & MLoc

I'm assuming that entry_date is a text field. If it's a date field, you
should be using

sqltxt = "insert into reports.MSS_Analysis_Master select * from " & _
mYear & " where entry_date >=" & _
Format(Me.DtFrom, "\#yyyy\-mm\-dd\#) & " And entry_date <=" & _
Format(Me.DtTo, "\#yyyy\-mm\-dd\#") & _
" and ShLoc =" & MLoc