VBA Query code issue........

[David]

Hi,

I have the following SQL string, but cannot get it working:

strSQL = "SELECT * FROM [pcbforecast] where [pcbforecast].[JobNumber] =
'" & [Forms]![PCBShipDateO]![PCBForecast].[Form]![JobNumber] & "' and
[pcbforecast].[ShipETA] = #" &
[Forms]![PCBShipDateO]![PCBForecast].[Form]![ShipETA] & "#"
JobNumber = Number
ShipETA = Date/Time

Basically, as a user tabs of a field on a continuous form, the system
need to test if they have already entered that same date for this
JobNumber previously.

The fields on the form are the same fields from the table which we are
testing against.
Appreciate your help on this.

Thanks

David

[Lyle Fairfield]

"David" <go********@googlemail.com> wrote in news:1134992586.697587.136880
@g44g2000cwa.googlegroups.com:

Hi,

I have the following SQL string, but cannot get it working:

strSQL = "SELECT * FROM [pcbforecast] where [pcbforecast].[JobNumber] =
'" & [Forms]![PCBShipDateO]![PCBForecast].[Form]![JobNumber] & "' and
[pcbforecast].[ShipETA] = #" &
[Forms]![PCBShipDateO]![PCBForecast].[Form]![ShipETA] & "#"

Probably you should not put single quotes around a number.

The date thing may or may not be a problem; you may have to format it to be
SURE you have something JET SQL will understand.

And, how do you deal with nulls?

--
Lyle Fairfield

[David W. Fenton]

"David" <go********@googlemail.com> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

I have the following SQL string, but cannot get it working:

strSQL = "SELECT * FROM [pcbforecast] where
[pcbforecast].[JobNumber] = '" &
[Forms]![PCBShipDateO]![PCBForecast].[Form]![JobNumber] & "' and
[pcbforecast].[ShipETA] = #" &
[Forms]![PCBShipDateO]![PCBForecast].[Form]![ShipETA] & "#"
JobNumber = Number
ShipETA = Date/Time

If JobNumber is *not* text, then you need to get rid of the single
quotes:

"where [pcbforecast].[JobNumber] = "
& [Forms]![PCBShipDateO]![PCBForecast].[Form]![JobNumber]

I can remember back when the issue of when to use quotes and when
not to use them was a complete mystery. Now the distinction is so
clear to me that I can't remember *how* it could ever have been a
mystery.

Someday, you'll be there, too.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/