Format Date

[Ragah21]

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

 

[CaptainD]

There are control issues with that method that you would need to check for since it lets the person enter the values.
For example, what if they enter "132007" or "!12007".

To make it easier, why not set up two combo boxes, one with numbers from 1-12 and the other populate with a range of years. The person can only select what you provide.

 

[Ragah21]

Because it's A propmt text box? if they enter the wrong data then its there problem? Thanks

 

[JerryKlmns]

Public Sub Which_Year(ByVal strANDClause As String)
    Dim qdfCurr As DAO.QueryDef
    Dim strPrompt As String
    Dim strSQL As String
   strPrompt = InputBox("Enter Month of Year in MMYYYY  format.", "Required Data")

   If Len(strPrompt) = 6 Then
      If IsDate(right(strPrompt,4) & "-" & Left(strPrompt, 2) & "-01") Then
         strSQL = "select * from Requests " & _
                  "where Format([Submit Date], "mmyyyy")=" & strPrompt & " AND "
         strSQL = strSQL & strANDClause
         Set qdfCurr = CurrentDb().QueryDefs("Step1_Member_Status")
         qdfCurr.SQL = strSQL
      Else
        MsgBox "Input was not an acceptable date or format"
      End If
   Else
      MsgBox "Input was not an acceptable date or format"
   End If
End Sub