formate date in sql

[FlyingDragon via AccessMonster.com]

hi all
i have a form with button when i click it execute the code

Private Sub Command12_Click()
On Error GoTo Err_cmdRunQuery_Click
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = " SELECT Cars.CarNum,Cars.CarKindID,format([Enter Your Date] FROM
Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] & """"
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " [CarKindID] like """ & Me![CarKindID] & """"

'*** delete the previous query
db.QueryDefs.Delete "SerialNum"
Set qdf = db.CreateQueryDef("SerialNum", strSQL)

'*** open the query

DoCmd.OpenReport "serialnum1", acViewPreview

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

my 2 problem is

1 - I want to force the user to enter date when he prompt for [Enter Your
Date]

2 - I want to show the date in report in format yyyy/m/d

thanks for all
(sorry about my english)

 

[MGFoster]

hi all
i have a form with button when i click it execute the code

Private Sub Command12_Click()
On Error GoTo Err_cmdRunQuery_Click
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = " SELECT Cars.CarNum,Cars.CarKindID,format([Enter Your Date] FROM
Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] & """"
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " [CarKindID] like """ & Me![CarKindID] & """"

'*** delete the previous query
db.QueryDefs.Delete "SerialNum"
Set qdf = db.CreateQueryDef("SerialNum", strSQL)

'*** open the query

DoCmd.OpenReport "serialnum1", acViewPreview

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
If Err.Number = 3265 Then '*** if the error is the query is missing
Resume Next '*** then skip the delete line and resume on
the next line
Else
MsgBox Err.Description '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub

my 2 problem is

1 - I want to force the user to enter date when he prompt for [Enter Your
Date]

2 - I want to show the date in report in format yyyy/m/d

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

Your syntax in the creation of the SQL string is incorrect. Try it like
this:

strSQL = "PARAMETERS [Enter Your Date] Date; " & _
"SELECT CarNum, CarKindID, " & _
"Format([Enter Your Date], "yyyy/m/d") " & _
"FROM Cars " & _
"WHERE [CarNum] Like '" & Me![CarNum] & "' " & _
"AND [CarKindID] Like '" & Me![CarKindID] & "'"

What's this: --> ??? Me![cboLogical]

You had Me!cboLogical just hanging in the middle of your WHERE clause.
I took it out since I didn't know which column in Cars would be the
comparison.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSfAyE4echKqOuFEgEQKoxwCfdByk9c62Reba9upuRVFpLDfPA1gAoK+0
3xcuUoifb8Can9JF5kT56RhQ
=eoUp
-----END PGP SIGNATURE-----

 

[FlyingDragon via AccessMonster.com]

hi all
i have a form with button when i click it execute the code

[quoted text clipped - 41 lines]

2 - I want to show the date in report in format yyyy/m/d

Your syntax in the creation of the SQL string is incorrect. Try it like
this:

strSQL = "PARAMETERS [Enter Your Date] Date; " & _
"SELECT CarNum, CarKindID, " & _
"Format([Enter Your Date], "yyyy/m/d") " & _
"FROM Cars " & _
"WHERE [CarNum] Like '" & Me![CarNum] & "' " & _
"AND [CarKindID] Like '" & Me![CarKindID] & "'"

What's this: --> ??? Me![cboLogical]

You had Me!cboLogical just hanging in the middle of your WHERE clause.
I took it out since I didn't know which column in Cars would be the
comparison.

HTH,

Me![cboLogical] the textbox has (and) and (or)
my code works well except my problems

i tried your code put it gives me error on "yyyy"
thanks

 

[John Spencer MVP]

I think your SQL statement is incorrect. It probably should read more like
the following:

strSQL = " SELECT Cars.CarNum,Cars.CarKindID" & _
", format([Enter Your Date],""yyyy/m/d"") " & _
" FROM Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] & """ AND "
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " AND [CarKindID] like """ & Me![CarKindID] & """"

Note the addition of the ANDs in the WHERE clause and the close parentheses
and the double quote marks around the format specification.

When I build an SQL string and it doesn't work, I usually add
Debug.Print strSQL
to my code after I've built the string. That way I can examine the SQL string
for syntax errors. I also can copy the string and paste it into a query and
click run to see if it works. It it fails the error message is often more
informative about where the problem is.



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[MGFoster]

hi all
i have a form with button when i click it execute the code

[quoted text clipped - 41 lines]

2 - I want to show the date in report in format yyyy/m/d

Your syntax in the creation of the SQL string is incorrect. Try it like
this:

strSQL = "PARAMETERS [Enter Your Date] Date; " & _
"SELECT CarNum, CarKindID, " & _
"Format([Enter Your Date], "yyyy/m/d") " & _
"FROM Cars " & _
"WHERE [CarNum] Like '" & Me![CarNum] & "' " & _
"AND [CarKindID] Like '" & Me![CarKindID] & "'"

What's this: --> ??? Me![cboLogical]

You had Me!cboLogical just hanging in the middle of your WHERE clause.
I took it out since I didn't know which column in Cars would be the
comparison.

HTH,

Me![cboLogical] the textbox has (and) and (or)
my code works well except my problems

i tried your code put it gives me error on "yyyy"
thanks

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

Sorry, I neglected to put the proper quotes around the format string.
It should be:

Format([Enter Your Date], ""yyyy/m/d"")

To have the report read the user-entered parameter you have to put a
TextBox on the report where you want the date to show. Set the
TextBox's properties to this:

Control Source: =[Enter Your Date]
Format: "yyyy/m/d"

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSfCz7YechKqOuFEgEQK5egCg5mFylHx9z3d2engZPjR7HbfHkmcAnRhv
nVuUOXDlOewQ1nuqc+LCgZdp
=2qVW
-----END PGP SIGNATURE-----

 

[FlyingDragon via AccessMonster.com]

I think your SQL statement is incorrect. It probably should read more like
the following:

strSQL = " SELECT Cars.CarNum,Cars.CarKindID" & _
", format([Enter Your Date],""yyyy/m/d"") " & _
" FROM Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] & """ AND "
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " AND [CarKindID] like """ & Me![CarKindID] & """"

Note the addition of the ANDs in the WHERE clause and the close parentheses
and the double quote marks around the format specification.

When I build an SQL string and it doesn't work, I usually add
Debug.Print strSQL
to my code after I've built the string. That way I can examine the SQL string
for syntax errors. I also can copy the string and paste it into a query and
click run to see if it works. It it fails the error message is often more
informative about where the problem is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

hi all
i have a form with button when i click it execute the code

[quoted text clipped - 44 lines]

thanks for all
(sorry about my english)

sorry i try but the textbox in report still show me the date format that i
dont want it show me d/m/yyyy

 

[John Spencer]

If you want the format in the report to be yyyy/m/d then you set the
format of the control in the report to that format.

You MIGHT be able to use something like the following in the query to
return a string that exactly matches the user's input.

StrSQL = "Parameters [Enter Your Date] Text(255);" & _
StrSQL = StrSQL & " SELECT Cars.CarNum,Cars.CarKindID" & _
", Format(CDate([Enter Your Date]),""yyyy/m/d"") " & _
" FROM Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] &
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " [CarKindID] like """ & Me![CarKindID] & """"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I think your SQL statement is incorrect. It probably should read more like
the following:

strSQL = " SELECT Cars.CarNum,Cars.CarKindID" & _
", format([Enter Your Date],""yyyy/m/d"") " & _
" FROM Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] & """ AND "
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " AND [CarKindID] like """ & Me![CarKindID] & """"

Note the addition of the ANDs in the WHERE clause and the close parentheses
and the double quote marks around the format specification.

When I build an SQL string and it doesn't work, I usually add
Debug.Print strSQL
to my code after I've built the string. That way I can examine the SQL string
for syntax errors. I also can copy the string and paste it into a query and
click run to see if it works. It it fails the error message is often more
informative about where the problem is.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

hi all
i have a form with button when i click it execute the code

[quoted text clipped - 44 lines]

thanks for all
(sorry about my english)

sorry i try but the textbox in report still show me the date format that i
dont want it show me d/m/yyyy

 

[FlyingDragon via AccessMonster.com]

If you want the format in the report to be yyyy/m/d then you set the
format of the control in the report to that format.

You MIGHT be able to use something like the following in the query to
return a string that exactly matches the user's input.

StrSQL = "Parameters [Enter Your Date] Text(255);" & _
StrSQL = StrSQL & " SELECT Cars.CarNum,Cars.CarKindID" & _
", Format(CDate([Enter Your Date]),""yyyy/m/d"") " & _
" FROM Cars WHERE "
strSQL = strSQL & "[CarNum] like """ & Me![CarNum] &
strSQL = strSQL & Me![cboLogical]
strSQL = strSQL & " [CarKindID] like """ & Me![CarKindID] & """"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
[quoted text clipped - 28 lines]

sorry i try but the textbox in report still show me the date format that i
dont want it show me d/m/yyyy

many thanks for you it worked