A questionf format

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

    A questionf format

    I am entering dates in to form using the dd/mm/yy format, however I am
    using a SQL which involves searching for those dates. I have formatted
    the dates in the SQL ot the US format of mm/dd/yy as follows -

    strSearch = "Select * FROM tblReservations where [RoomNo] = " &
    F![tblTemp]![RoomNo] _
    & " AND [BeginDate] >= #" & Format(F![tblTemp]![Outdate],
    "mm/dd/yy") & "# " _
    & " OR [EndDate] <= #" & Format(F![tblTemp]![Indate], "mm/dd/yy") &
    "#;"

    There appears to be a conflict when I use the SQL statement. What
    should I be doing?

    I havethe regional setting on dd/mm/yy. Does this make a difference.
    This is driving me bonkers.... far more than usual.

    TIA - Ray
    Tags: None
    • MGFoster
      #2
      Re: A questionf format

      Ray wrote:
      [color=blue]
      > I am entering dates in to form using the dd/mm/yy format, however I am
      > using a SQL which involves searching for those dates. I have formatted
      > the dates in the SQL ot the US format of mm/dd/yy as follows -
      >
      > strSearch = "Select * FROM tblReservations where [RoomNo] = " &
      > F![tblTemp]![RoomNo] _
      > & " AND [BeginDate] >= #" & Format(F![tblTemp]![Outdate],
      > "mm/dd/yy") & "# " _
      > & " OR [EndDate] <= #" & Format(F![tblTemp]![Indate], "mm/dd/yy") &
      > "#;"
      >
      > There appears to be a conflict when I use the SQL statement. What
      > should I be doing?
      >
      > I havethe regional setting on dd/mm/yy. Does this make a difference.
      > This is driving me bonkers.... far more than usual.[/color]

      -----BEGIN PGP SIGNED MESSAGE-----
      Hash: SHA1

      What's this "F![tblTemp]![Outdate]" syntax? If you are referencing a
      control on a form the correct syntax is:

      Forms!FormName! ControlName

      If you are referencing a column in another table the correct syntax is:

      TableName.Colum nName

      and, you have to include the table name in the SQL FROM clause.

      Advice: use the correct data type prefixes. IOW, don't use "tbl" for a
      form name prefix, because "tbl" is the common prefix to a table name.

      - --
      MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
      Oakland, CA (USA)

      -----BEGIN PGP SIGNATURE-----
      Version: PGP for Personal Privacy 5.0
      Charset: noconv

      iQA/AwUBQGzXaIechKq OuFEgEQK7SACeO/482Y2Ramqsl1rqx cfaTtoFmXcAnizA
      oKQZwO8ub8LrfW3 PShnGIqUJ
      =FWsu
      -----END PGP SIGNATURE-----

        Comment

        • R.A.Balder
          #3
          Re: A questionf format

          And what is this Format doing in an sql code. I think you make the same
          mistake about dates as many other. Dates are stored as date values - no
          matter what format they are entered or displayed in. When you search for
          them, you search for the value. When you set the format to any date format
          in the form, you should just reference the field directly. (But if you
          only use an input mask and have it as a text field - then you need to
          convert it to a date value using DateValue() , and I expect that the field
          in the table is a date type.)

          Brgds
          Rolfern

          "MGFoster" <me@privacy.com > wrote in message
          news:xH4bc.1198 1$lt2.11614@new sread1.news.pas .earthlink.net. ..[color=blue]
          > Ray wrote:
          >[color=green]
          > > I am entering dates in to form using the dd/mm/yy format, however I am
          > > using a SQL which involves searching for those dates. I have formatted
          > > the dates in the SQL ot the US format of mm/dd/yy as follows -
          > >
          > > strSearch = "Select * FROM tblReservations where [RoomNo] = " &
          > > F![tblTemp]![RoomNo] _
          > > & " AND [BeginDate] >= #" & Format(F![tblTemp]![Outdate],
          > > "mm/dd/yy") & "# " _
          > > & " OR [EndDate] <= #" & Format(F![tblTemp]![Indate], "mm/dd/yy") &
          > > "#;"
          > >
          > > There appears to be a conflict when I use the SQL statement. What
          > > should I be doing?
          > >
          > > I havethe regional setting on dd/mm/yy. Does this make a difference.
          > > This is driving me bonkers.... far more than usual.[/color]
          >
          > -----BEGIN PGP SIGNED MESSAGE-----
          > Hash: SHA1
          >
          > What's this "F![tblTemp]![Outdate]" syntax? If you are referencing a
          > control on a form the correct syntax is:
          >
          > Forms!FormName! ControlName
          >
          > If you are referencing a column in another table the correct syntax is:
          >
          > TableName.Colum nName
          >
          > and, you have to include the table name in the SQL FROM clause.
          >
          > Advice: use the correct data type prefixes. IOW, don't use "tbl" for a
          > form name prefix, because "tbl" is the common prefix to a table name.
          >
          > - --
          > MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
          > Oakland, CA (USA)
          >
          > -----BEGIN PGP SIGNATURE-----
          > Version: PGP for Personal Privacy 5.0
          > Charset: noconv
          >
          > iQA/AwUBQGzXaIechKq OuFEgEQK7SACeO/482Y2Ramqsl1rqx cfaTtoFmXcAnizA
          > oKQZwO8ub8LrfW3 PShnGIqUJ
          > =FWsu
          > -----END PGP SIGNATURE-----
          >[/color]


            Comment

            Previous template Next
            Working...