Date Format for a Query

[cooldisk2005]

All,
I have the following code that I need the dateBegin and dateEnd field to have a date format like this: 2006-3-1.

I have that in the code below for those fields, but when you actually look at the design view of the query it is formatting the date like this: >=#3/1/2006#, which is not what I want. Again I want: 2006-3-1.

Here is the code

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the Query definition
'Constant Select statement for the Query definition
strSQL = "SELECT tbl_showmoney.regID, tbl_showmoney.dateEntered, tbl_showmoney.name, tbl_showmoney.company, tbl_showmoney.mailingAddress, tbl_showmoney.city, tbl_showmoney.stateCan, tbl_showmoney.zipCode, tbl_showmoney.country, tbl_showmoney.telephoneNo, tbl_showmoney.faxNo, tbl_showmoney.email, tbl_showmoney.contype, tbl_showmoney.totalCamp, tbl_showmoney.idMtg, tbl_showmoney.day1Con, tbl_showmoney.day2Con, tbl_showmoney.day3Con, tbl_showmoney.day4Con, tbl_showmoney.day5Con, tbl_showmoney.day6Con, tbl_showmoney.day7Con, tbl_showmoney.bkstCount, tbl_showmoney.lunchTickets, tbl_showmoney.dinnerTickets, tbl_showmoney.drinkTickets " & _
"FROM tbl_showmoney"

strWhere = "WHERE"

strOrder = " ORDER BY tbl_showmoney.regID;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.name1) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (tbl_showmoney.name) Like '*" & Me.name1 & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.company) Then
strWhere = strWhere & " (tbl_showmoney.company) Like '*" & Me.company & "*'  AND"
End If

If Not IsNull(Me.contype) Then
strWhere = strWhere & " (tbl_showmoney.contype) Like '*" & Me.contype & "*'  AND"
End If

If Not IsNull(Me.dateBegin) Then
strWhere = strWhere & " (tbl_showmoney.dateEntered) >=#" & Format(Me.dateBegin, "yyyy-m-d") & "# AND"
End If

If Not IsNull(Me.dateEnd) Then
strWhere = strWhere & " (tbl_showmoney.dateEntered) <=#" & Format(Me.dateEnd, "yyyy-m-d") & "# AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 4)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_showmoney")
   qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
   
'Open the Query
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter
 
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_showmoney", dbOpenDynaset)
 
With rst
  If .RecordCount > 0 Then 'What you want done
    DoCmd.OpenForm ("frm_registrationDeskSearch"), acNormal, "[regID]"
    DoCmd.Close acForm, "frm_search"
    
    
  Else
    MsgBox "There is no data that meets your criteria.  Please try again.", vbOKOnly, "No Data Found" 'What you want done
    name1.Value = ""
    company.Value = ""
    contype.Value = ""
    name1.SetFocus
  End If
End With

Set dbs = Nothing
Set rst = Nothing

   

End Sub

How can I fix the code to give me the format I need?

I have been trying all sorts of things and it is just not working!!!

Please help anyone, and it will surely be appreciated.

Thanks in advance!!!

 

[r937]

date/time fields in access show up in two places -- when you are specifying values to be inserted or updated or searched for, and when you are displaying stored values

in between, the values are actually stored as integers

now, on the way in, you can specify a date/time value in a myriad of formats, and as long as the database can figure out which date you meant, it will convert that date happily

and on the way out, it will use some default format, which can be changed by you, or else you could also use the FORMAT function to display whatever you want

by the way, i noticed that your code to build the WHERE clause won't work if none of the text boxes has a value -- your code attempts to remove the last AND but it doesn't remove an empty WHERE

there's an easy way around this

start with strWhere = "WHERE 1=1"

then you can append additional conditions, each beginning with AND, and you won't have to remove anything after reviewing all the text boxes, and the query will run nicely if no additional conditions were added





r937.com | rudy.ca