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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • David
    #1

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

    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

    Tags: None
    • Lyle Fairfield
      #2
      Re: VBA Query code issue........

      "David" <gordon.dtr@goo glemail.com> wrote in news:1134992586 .697587.136880
      @g44g2000cwa.go oglegroups.com:
      [color=blue]
      > 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] & "#"[/color]

      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

        Comment

        • David W. Fenton
          #3
          Re: VBA Query code issue........

          "David" <gordon.dtr@goo glemail.com> wrote in
          news:1134992586 .697587.136880@ g44g2000cwa.goo glegroups.com:
          [color=blue]
          > 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[/color]

          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/

            Comment

            Previous template Next
            Working...