QUERY Question

[DS]

Whenever you have a Query (SQL Statement) that has a field that is
getting its information from a combobox, (The combobox has 2 fields, the
first is bound, the second is unbound and its a medium date format) and
you are comparing it to a textbox that has a medium date format...well
the problem is, the query is returning the firstfield and I need the
second field (medium date) in order for the Query to work. How can I do
this?

I tried refering to the second field thustly....
Forms!SalesDetails!StartTime.Column(1) but I keep getting an Error.

Any help appreciated.
Thanks
DS

 

[Douglas J Steele]

What's the error you're getting? And how are you doing this: did you put
Forms!SalesDetails!StartTime.Column(1) as a criteria in a query, or are you
build SQL in VBA code?

 

[DS]

What's the error you're getting? And how are you doing this: did you put
Forms!SalesDetails!StartTime.Column(1) as a criteria in a query, or are you
build SQL in VBA code?

I'm building the SQL code through the control source. Then in Query
View I'm putting on the Criteria. I'm not saving it so it stays in SQL
except when I try to close it is says invalid syntax. If I leave it
just as it is the fields revert back to the bound field of the combo box
and I see the ID but not the time.
Thanks
DS

 

[DS]

I'm building the SQL code through the control source. Then in Query
View I'm putting on the Criteria. I'm not saving it so it stays in SQL
except when I try to close it is says invalid syntax. If I leave it
just as it is the fields revert back to the bound field of the combo box
and I see the ID but not the time.
Thanks
DS

Just a little more, Enerything works fine as long as I input the time
directly by myself on the form, the minute I try to use a combobox to
use pre-set times thats when things get screwy.
Thanks
DS

 

[Douglas J. Steele]

Post the code that you're using.

 

[DS]

Post the code that you're using.
SELECT DISTINCT Menus.MenuID, Menus.Menu, MenuDetails.MenuDay, Terminal.TerminalName, MenuDetails.Startime, MenuDetails.EndTime

FROM ((Menus LEFT JOIN MenuDetails ON Menus.MenuID = MenuDetails.MenuID)
LEFT JOIN Terminal ON MenuDetails.TerminalID = Terminal.TerminalID)
INNER JOIN Items ON Menus.MenuID = Items.MenuID
GROUP BY Menus.MenuID, Menus.Menu, MenuDetails.MenuDay,
Terminal.TerminalName, MenuDetails.Startime, MenuDetails.EndTime
HAVING (((MenuDetails.MenuDay)=[Text9]) AND
((Terminal.TerminalName)=Environ("ComputerName")) AND
((MenuDetails.Startime)<[Text11]) AND ((MenuDetails.EndTime)>[Text11]));


This is on the Row Source of the Data tab of the Listbox.

Digging deeper..The fields [StartTie] and [EndTime] in the tables are
setup as Time/Date fields, however; when I build the form and turn these
fields into combo boxs and call the info from the Time table. It wont
allow me to enter these times into these 2 fields, It says wrong type of
data, they are both Medium Time format. And as I said earlier (See
Above) The [StartTime]and [EndTime] fields both return the ID of the
Time Table. The Time Table has 2 fields only. TimeID and Time, TimeID
is autonumber and Time is Time/Date with Medium Time Format. I hope
this helps.
Thank you for helping, it is appreciated.
DS

 

[Douglas J. Steele]

I wouldn't have expected that SQL to work, as I didn't think you could use
Environ in a query like that.

Try creating a query and using the query as the row source, rather than
in-line SQL like that. In the query, define the data type of the parameters
as Date, so that Access will know what to do with them.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Post the code that you're using.
SELECT DISTINCT Menus.MenuID, Menus.Menu, MenuDetails.MenuDay,
Terminal.TerminalName, MenuDetails.Startime, MenuDetails.EndTime

FROM ((Menus LEFT JOIN MenuDetails ON Menus.MenuID = MenuDetails.MenuID)
LEFT JOIN Terminal ON MenuDetails.TerminalID = Terminal.TerminalID) INNER
JOIN Items ON Menus.MenuID = Items.MenuID
GROUP BY Menus.MenuID, Menus.Menu, MenuDetails.MenuDay,
Terminal.TerminalName, MenuDetails.Startime, MenuDetails.EndTime
HAVING (((MenuDetails.MenuDay)=[Text9]) AND
((Terminal.TerminalName)=Environ("ComputerName")) AND
((MenuDetails.Startime)<[Text11]) AND ((MenuDetails.EndTime)>[Text11]));


This is on the Row Source of the Data tab of the Listbox.

Digging deeper..The fields [StartTie] and [EndTime] in the tables are
setup as Time/Date fields, however; when I build the form and turn these
fields into combo boxs and call the info from the Time table. It wont
allow me to enter these times into these 2 fields, It says wrong type of
data, they are both Medium Time format. And as I said earlier (See Above)
The [StartTime]and [EndTime] fields both return the ID of the Time Table.
The Time Table has 2 fields only. TimeID and Time, TimeID is autonumber
and Time is Time/Date with Medium Time Format. I hope this helps.
Thank you for helping, it is appreciated.
DS

 

[DS]

I wouldn't have expected that SQL to work, as I didn't think you could use
Environ in a query like that.

Try creating a query and using the query as the row source, rather than
in-line SQL like that. In the query, define the data type of the parameters
as Date, so that Access will know what to do with them.

Will do. Thank You.
DS