Select AS query problem

[gambit32]

I have a table that I want to query on. In this table for some reason,
the original designer used separate fields for Month/Day/Year. So one
field for Month, another for Day, and another for Year.

The form I created has two calendars on it. The calendars allow the
user to select two dates and search for data between those dates. I
created a workaround but I'd like to not have to use a workaround.

I was thinking that a Select AS clause(?) would allow me to take the
three fields and truncate them into one date field, thus allowing me to
query on the new field from the AS portion of the query based on what's
selected in the calendars. I'm just not sure on the syntax. All help
is appreciated.

Code below:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String, strWhere As String
Dim i As Integer

Set db = CurrentDb

'*** create the query based on the information on the form
strSQL = "SELECT (TTT_TIME_TIMETTRACKER.fld_day || '/' ||
TTT_TIME_TIMETTRACKER.fld_month || '/' ||
TTT_TIME_TIMETTRACKER.fld_year) AS TTT_TIME_TIMETTRACKER.fld_date,
TTT_T,TTT_TIME_TIMETTRACKER.fld_break_mins,TTT_TIME_TIMETTRACKER.fld_break_hrs,TTT_TIME_TIMETTRACKER.fld_date,TTT_TIME_TIMETTRACKER.fld_client,TTT_TIME_TIMETTRACKER.fld_project,TTT_TIME_TIMETTRACKER.fld_subproject,TTT_TIME_TIMETTRACKER.fld_currency,TTT_TIME_TIMETTRACKER.fld_duration_hrs,TTT_TIME_TIMETTRACKER.fld_duration_mins,TTT_TIME_TIMETTRACKER.fld_note,
TTT_TIME_TIMETTRACKER.fld_rate,TTT_TIME_TIMETTRACKER.fld_amount FROM
TTT_TIME_TIMETTRACKER "
strWhere = "Where ((TTT_TIME_TIMETTRACKER.fld_date) Between
Forms!aspform2!date1 And Forms!aspform2!date2) and
TTT_TIME_TIMETTRACKER.fld_client IN ("
For i = 0 To listclient.ListCount - 1
If listclient.Selected(i) Then
strWhere = strWhere & "'" & listclient.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strWhere = strWhere & " AND TTT_TIME_TIMETTRACKER.fld_currency IN ("
For i = 0 To listemployee.ListCount - 1
If listemployee.Selected(i) Then
strWhere = strWhere & "'" & listemployee.Column(0, i) & "', "
End If
Next i
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
strSQL = strSQL & strWhere
'*** MsgBox strSQL

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

'*** open the query
'*** DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

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 "Please check that you chose a client and an employeee!
Try again." '*** write out the error and exit the sub
Resume Exit_cmdRunQuery_Click
End If
End Sub


....Thanks again!
Mark

 

[pietlinden]

Mark,
Looks like you should be able to use DateSerial.

?dateserial(2004,12,31)
12/31/2004

 

[gambit32]

Forgive but I am a newbie. I was only able to get this far with the
help of you fine folks. How would I implement the DateSerial function?
Thanks for responding.

Mark

 

[pietlinden]

Forgive but I am a newbie. I was only able to get this far with the
help of you fine folks. How would I implement the DateSerial function?
Thanks for responding.

Mark

Instead of this:
strSQL = "SELECT (TTT_TIME_TIMETTRACKER.fld_day || '/' ||
TTT_TIME_TIMETTRACKER.fld_month || '/' ||
TTT_TIME_TIMETTRACKER.fld_year) AS TTT_TIME

use
SELECT DateSerial(TT.fld_year,TT.fld_month,TT.fld_day) As RepairedDate
FROM TTT_TIME_TIMETTRACKER AS TT
....

wow. who came up with fieldnames that long... I must be getting
lazy... I lose interest after about the 6th character...