Format Date

[Guest]

I have a prompt text box, and I would like a person to enter Month & Year
(eg, 022007) Instead of Month only! I tried to use the fromat date but it
didnt work.

The code Below:

Public Sub Which_Year(ByVal strANDClause As String)
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of Year you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Year in YYYY format.", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _

"where Month([Submit Date])=" & strPrompt & " AND "

strSQL = strSQL & strANDClause

Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub

 

[Guest]

If the user enter a format of mmyyyy then try to change the code to

" where Format([Submit Date],'mmyyyy')=" & strPrompt & " AND "

 

[Guest]

its giving me an error, because its match a number to string. Can I change my
input string to number?

If the user enter a format of mmyyyy then try to change the code to

" where Format([Submit Date],'mmyyyy')=" & strPrompt & " AND "

--
Good Luck
BS"D

I have a prompt text box, and I would like a person to enter Month & Year
(eg, 022007) Instead of Month only! I tried to use the fromat date but it
didnt work.

The code Below:

Public Sub Which_Year(ByVal strANDClause As String)
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of Year you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Year in YYYY format.", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _

"where Month([Submit Date])=" & strPrompt & " AND "

strSQL = strSQL & strANDClause

Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub

 

[Guest]

Sorry, my mistake
Try adding a single quote before and after the strPrompt to make it a string
criteria

" where Format([Submit Date],'mmyyyy')='" & strPrompt & "' AND "


--
Good Luck
BS"D

its giving me an error, because its match a number to string. Can I change my
input string to number?

If the user enter a format of mmyyyy then try to change the code to

" where Format([Submit Date],'mmyyyy')=" & strPrompt & " AND "

--
Good Luck
BS"D

I have a prompt text box, and I would like a person to enter Month & Year
(eg, 022007) Instead of Month only! I tried to use the fromat date but it
didnt work.

The code Below:

Public Sub Which_Year(ByVal strANDClause As String)
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of Year you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Year in YYYY format.", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _

"where Month([Submit Date])=" & strPrompt & " AND "

strSQL = strSQL & strANDClause

Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub

 

[Guest]

Thanks a million it worked fine....

Sorry, my mistake
Try adding a single quote before and after the strPrompt to make it a string
criteria

" where Format([Submit Date],'mmyyyy')='" & strPrompt & "' AND "


--
Good Luck
BS"D

its giving me an error, because its match a number to string. Can I change my
input string to number?

If the user enter a format of mmyyyy then try to change the code to

" where Format([Submit Date],'mmyyyy')=" & strPrompt & " AND "

--
Good Luck
BS"D


:

I have a prompt text box, and I would like a person to enter Month & Year
(eg, 022007) Instead of Month only! I tried to use the fromat date but it
didnt work.

The code Below:

Public Sub Which_Year(ByVal strANDClause As String)
Dim qdfCurr As DAO.QueryDef
Dim strPrompt As String
Dim strSQL As String
'Input the random number of Year you want access
'by changeing the Step1_Member_Status query
strPrompt = InputBox("Enter Year in YYYY format.", "Required Data")

If Len(strPrompt) > 0 Then
If IsNumeric(strPrompt) Then
strSQL = "select * from Requests " & _

"where Month([Submit Date])=" & strPrompt & " AND "

strSQL = strSQL & strANDClause

Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
qdfCurr.SQL = strSQL
End If
End If
End Sub