Data Reports and functions

[Nadine]

I am having trouble with the data reports designer in VB6 again! I have an access data base, and I am using the dataenvironment. I want the user to be able to enter in a datetime (3/3/1999 10:40 am) into a text box, and pass the parameter to a data report...I am using the system date to check it now. Basically I want a date range, and I want the user to be able to enter one or more datetime to be specified.Here is the code:
SELECT Distinct tblDailyWorksheet.Activity, tblDailyWorksheet.ArrivalNumber, tblDailyWorksheet.ArrivalTo, tblDailyWorksheet.DepartureFrom, tblDailyWorksheet.Invoiced, tblDailyWorksheet.MovementDateTime, tblDailyWorksheet.MovementType, tblCustomer.Province, tblInvoice.Berth, tblVesselMaster.VesselName FROM tblCustomer, tblInvoice, tblVesselMaster, tblDailyWorksheet WHERE tblCustomer.CustomerNumber = tblInvoice.CustomerNumber AND tblCustomer.CustomerNumber = tblVesselMaster.CustomerNumber AND tblDailyWorksheet.SpecialInstruction, tblCustomer.Address, tblCustomer.City, tblCustomer.Customer, tblCustomer.PostalCode, tblVesselMaster.VesselNumber = tblDailyWorksheet.VesselNumber AND tblDailyWorkSheet.MovementDateTime BETWEEN now() AND (tblDailyWorkSheet.MovementDateTime=?)

However all the records in the tables come up, it is not meeting the between criteria. How do I restrict the queries in a manner that will work?
Neither date() nor now() will work...it doesn't seem to be accepting any functions.
Help! Nadine
[email protected]

[Lothar Haensler]

you can modify the CommandText property of the Command object in your Data environment before executing the Data report as in:
DataEnvironment1.Commands(1).CommandText = "select Name, vorname, strasse, ort from lahadr where name like 'meier%'"
DataReport1.Show


This can be easily adopted to your needs. Simply modify the BETWEEN clause.
It might be more elegant to use a parametrized query, but the technique is the same.

Becarefull !!

Now() and Date() function depend on your system setting. That is dd/mm/yy or mm/dd/yy. There is a good way to work with day and time in Access is using date time with format #mm/dd/yy#. That format is not depend on your system setting.

H.Q.Cuong