Need 2 Query calculated fields to act as Dates 1

[cimoli]

I am having trouble getting 2 fields in a query to become DATES.

I have a form named frmTravelReports with 2 unbound fields. txtTripDateLookup and txtNightsIN.
Answer examples are 11/2/2013 and 7. Which means the tripdate and the number of nights for the trip.

I have a subform named sfrm7962People on the form.
In the subforms query, I made 2 calculated fields which i want to be DATES
that i would use for some purpose.

Here is what I have for the 2 query fields that i want to see as Dates.

txtSplinterDateIN: [forms]![frmTravelReports]![txtTripDateLookup]
EG I was hoping to see 11/2/2013

txtSplinterDateOUT: [forms]![frmTravelReports]![txtTripDateLookup]+[forms]![frmTravelReports]![txtNightsIn]
EG I was hoping for 11/9/2013.

Can you help to make them into dates? Thanks much.

 

[cimoli]

Maybe the 2 calculated query fields need some kind of DateAdd ??

Here is what I have for the 2 query fields that i want to see as Dates.

txtSplinterDateIN: [forms]![frmTravelReports]![txtTripDateLookup]
EG I was hoping to see 11/2/2013

txtSplinterDateOUT: [forms]![frmTravelReports]![txtTripDateLookup]+[forms]![frmTravelReports]![txtNightsIn]
EG I was hoping for 11/9/2013.

thanks

 

[lameid]

The better way would be to take the form reference and use it as a parameter for your query and specify the datatype there.

More generically, you can also use the CDATE function to convert to a date.

 

[cimoli]

I tried your 1st way: a parameter field name and Date type.
Iput the 2 names into the query. txtSplinterDateIN and txtSplinterDateOUT as Date.
but when i ran the qry, it asked for these 2 date answers; it did not wait to
read my other criteria of TripID. So i canned it.

Tried your 2nd way: I probably did not code it right. Could you check below to see if my query
code is correct or not?

txtSplinterDateIN: CDate([forms]![frmTravelReports]![txtTripDateLookup])


Notice in the 2nd calculated field that i used 2 cdate's. I am adding a real date with a number.
eg 11/2/2013 plus 7 days, trying to yield an answer of 11/9/2013. but did not work.
txtSplinterDateOUT: CDate([forms]![frmTravelReports]![txtTripDateLookup]+CDate([forms]![frmTravelReports]![txtNightsIn]))

thanks

 

[PHV]

What is your actual SQL code?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

I read more on the internet about Cdate. I tried the following but still short of the answer.

txtSplinterDateIN: Format(CDate([forms]![frmTravelReports]![txtTripDateLookup]),"mm/dd/yyyy")

i got an answer of 11/2/2013 which is good but it was left justified which means it does NOT
think it is a date but still text.

however, next one did not work right.
txtSplinterDateOUT: Format(CDate([forms]![frmTravelReports]![txtTripDateLookup]),"mm/dd/yyyy")+Format(CDate([forms]![frmTravelReports]![txtNightsIn]),"mm/dd/yyyy")

i get an answer of 11/02/201301/06/1900
as one big string. also left justified.

Help. thanks alot.

 

[cimoli]

sql of the query

SELECT tblDetail.ReservationID, tblDetail.RoomNum, tblDetail.RoomLead, tblDetail.RLnum, tblDetail.CompID, tblReservation.BusnID, tblDetail.ID, tblCustInfo.LASTNAME, tblCustInfo.FIRSTNAME, tblReservation.TripID, tblReservation.ResortID, Format(CDate([forms]![frmTravelReports]![txtTripDateLookup]),"mm/dd/yyyy") AS txtSplinterDateIN, Format(CDate([forms]![frmTravelReports]![txtTripDateLookup]),"mm/dd/yyyy")+Format(CDate([forms]![frmTravelReports]![txtNightsIn]),"mm/dd/yyyy") AS txtSplinterDateOUT, tblReservation.SplinterDateIN, tblDetail.Category, tblDetail.BedID, tblDetail.Remarks
FROM tblReservation INNER JOIN (tblCustInfo INNER JOIN tblDetail ON tblCustInfo.ID = tblDetail.ID) ON tblReservation.ReservationID = tblDetail.ReservationID
WHERE (((tblReservation.BusnID)=[forms]![frmTravelReports]![cboBusnID]) AND ((tblReservation.TripID)=[forms]![frmTravelReports]![cboTripId55]) AND ((tblDetail.Cancel)=False)) OR (((tblReservation.ResortID)=[forms]![frmTravelReports]![cboResort]) AND ((tblReservation.SplinterDateIN) Between [forms]![frmTravelReports]![txtSplinterDateIN] And [forms]![frmTravelReports]![txtSplinterDateOUT]) AND ((tblDetail.Cancel)=False))
ORDER BY tblDetail.ReservationID, tblDetail.RoomNum, tblDetail.RoomLead;

 

[PHV]

Perhaps this ?
txtSplinterDateIN: CDate([Forms]![frmTravelReports]![txtTripDateLookup])

txtSplinterDateOUT: CDate([Forms]![frmTravelReports]![txtTripDateLookup])+[Forms]![frmTravelReports]![txtNightsIn]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cimoli]

thanks so much.