A questionf format

[Ray]

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

[MGFoster]

Ray wrote:

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.

-----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.ColumnName

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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQGzXaIechKqOuFEgEQK7SACeO/482Y2Ramqsl1rqxcfaTtoFmXcAnizA
oKQZwO8ub8LrfW3PShnGIqUJ
=FWsu
-----END PGP SIGNATURE-----

[R.A.Balder]

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" <[e-mail]> wrote in message
news:xH*******************@newsread1.news.pas.eart hlink.net...

Ray wrote:

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.

-----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.ColumnName

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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQGzXaIechKqOuFEgEQK7SACeO/482Y2Ramqsl1rqxcfaTtoFmXcAnizA
oKQZwO8ub8LrfW3PShnGIqUJ
=FWsu
-----END PGP SIGNATURE-----