Criteria from form on calculated field

  • Thread starter Thread starter menube
  • Start date Start date
M

menube

I have a delivery date field in my query that is calculated. When I type the
criteria directly into the query exp. #10/20/08# everything works fine. If I
use a form to retrive the criteria it does not work. The problem only occurs
with the calcualted field. I'm new at this so I'm sure I'm missing
something. Any help would be appreciated.
 
Can you provide the SQL view of the query with the criteria from the form?
Can you tell us what you mean by "it does not work" which tells us almost
nothing?
 
I have a delivery date field in my query that is calculated. When I type the
criteria directly into the query exp. #10/20/08# everything works fine. If I
use a form to retrive the criteria it does not work. The problem only occurs
with the calcualted field. I'm new at this so I'm sure I'm missing
something. Any help would be appreciated.
Click to expand...

You're not giving us much to go on... but one suggestion: open the query in
design view. Rightclick the grey background of the tables, and select
Paramters. Put the *exact* paramter (e.g. [Forms]![MyForm]![txtStart]) in the
left column and select Date/Time as the type in the right column for each
criterion.
 
SELECT
IIf(Weekday([CODetails].[Date]-[PrepAdj])=1,[CODetails].[Date]-[PrepAdj]-2,IIf(Weekday([CODetails].[Date]-[PrepAdj])=7,[CODetails].[Date]-[PrepAdj]-1,[CODetails].[Date]-[PrepAdj]))
AS PrepDate, IIf([List Order]=10,[Vegitarians],[StudentCount]) AS Count2,
Customers.Vegitarians, PackSheetItems.[List order], PSJoinTable.PSItemID,
CustomerOrders.COID, CustomerOrders.CustomerID, CountTable.Division,
CountTable.Subdivison, CountTable.StudentCount, CustomerOrders.MealType,
CustomerOrders.ServingType, CustomerOrders.Month, CustomerOrders.Year,
CODetails.Date, CODetDet.MenuItemID, PackSheetItems.PSIPortion,
PackSheetItems.PSUOM, PackSheetItems.Department,
IIf(Weekday([CODetails].[Date]-[DelAdj])=1,[CODetails].[Date]-[DelAdj]-2,IIf(Weekday([CODetails].[Date]-[DelAdj])=7,[CODetails].[Date]-[DelAdj]-1,[CODetails].[Date]-[DelAdj])) AS DelDate, Weekday([CODetails].[Date]) AS Weekday
FROM (MenuItems INNER JOIN ((Customers INNER JOIN (CustomerOrders INNER JOIN
(CODetails INNER JOIN CountTable ON CODetails.CODetID = CountTable.CODetID)
ON CustomerOrders.COID = CODetails.COID) ON Customers.CustomerID =
CustomerOrders.CustomerID) INNER JOIN CODetDet ON CODetails.CODetID =
CODetDet.CODetID) ON MenuItems.MenuItemID = CODetDet.MenuItemID) INNER JOIN
(PackSheetItems INNER JOIN PSJoinTable ON PackSheetItems.PSItemID =
PSJoinTable.PSItemID) ON MenuItems.MenuItemID = PSJoinTable.MenuItemID
WHERE
(((IIf(Weekday([CODetails].[Date]-[PrepAdj])=1,[CODetails].[Date]-[PrepAdj]-2,IIf(Weekday([CODetails].[Date]-[PrepAdj])=7,[CODetails].[Date]-[PrepAdj]-1,[CODetails].[Date]-[PrepAdj])))=[Forms]![ReportInformation]![Text0]));
When I type the date criteria directly into the query, I get the all the
data for that date. If I ask the query to get the data form the form I get
no data at all. I can cut and past the request for data into a non
calculated field and I get the data for that date.

I hope this is the information that you need. Thanks for you help.
 
I expect John Vinson may have hit the nail on the head. IIf() returns a
variant data type and may treat the result as text.

I would also recommend changing the "Weekday([CODetails].[Date]) AS Weekday"
to not return a function name as a column heading. Perhaps
Weekday([CODetails].[Date]) AS Wkday
Year, Month, and Date are also function names and shouldn't be used as field
names. You should change them if not too difficult.
--
Duane Hookom
Microsoft Access MVP


menube said:
SELECT
IIf(Weekday([CODetails].[Date]-[PrepAdj])=1,[CODetails].[Date]-[PrepAdj]-2,IIf(Weekday([CODetails].[Date]-[PrepAdj])=7,[CODetails].[Date]-[PrepAdj]-1,[CODetails].[Date]-[PrepAdj]))
AS PrepDate, IIf([List Order]=10,[Vegitarians],[StudentCount]) AS Count2,
Customers.Vegitarians, PackSheetItems.[List order], PSJoinTable.PSItemID,
CustomerOrders.COID, CustomerOrders.CustomerID, CountTable.Division,
CountTable.Subdivison, CountTable.StudentCount, CustomerOrders.MealType,
CustomerOrders.ServingType, CustomerOrders.Month, CustomerOrders.Year,
CODetails.Date, CODetDet.MenuItemID, PackSheetItems.PSIPortion,
PackSheetItems.PSUOM, PackSheetItems.Department,
IIf(Weekday([CODetails].[Date]-[DelAdj])=1,[CODetails].[Date]-[DelAdj]-2,IIf(Weekday([CODetails].[Date]-[DelAdj])=7,[CODetails].[Date]-[DelAdj]-1,[CODetails].[Date]-[DelAdj])) AS DelDate, Weekday([CODetails].[Date]) AS Weekday
FROM (MenuItems INNER JOIN ((Customers INNER JOIN (CustomerOrders INNER JOIN
(CODetails INNER JOIN CountTable ON CODetails.CODetID = CountTable.CODetID)
ON CustomerOrders.COID = CODetails.COID) ON Customers.CustomerID =
CustomerOrders.CustomerID) INNER JOIN CODetDet ON CODetails.CODetID =
CODetDet.CODetID) ON MenuItems.MenuItemID = CODetDet.MenuItemID) INNER JOIN
(PackSheetItems INNER JOIN PSJoinTable ON PackSheetItems.PSItemID =
PSJoinTable.PSItemID) ON MenuItems.MenuItemID = PSJoinTable.MenuItemID
WHERE
(((IIf(Weekday([CODetails].[Date]-[PrepAdj])=1,[CODetails].[Date]-[PrepAdj]-2,IIf(Weekday([CODetails].[Date]-[PrepAdj])=7,[CODetails].[Date]-[PrepAdj]-1,[CODetails].[Date]-[PrepAdj])))=[Forms]![ReportInformation]![Text0]));
When I type the date criteria directly into the query, I get the all the
data for that date. If I ask the query to get the data form the form I get
no data at all. I can cut and past the request for data into a non
calculated field and I get the data for that date.

I hope this is the information that you need. Thanks for you help.
--
menube


Duane Hookom said:
Can you provide the SQL view of the query with the criteria from the form?
Can you tell us what you mean by "it does not work" which tells us almost
nothing?
Click to expand...
Click to expand...
 
Thanks I will give it a try.
--
menube


Duane Hookom said:
I expect John Vinson may have hit the nail on the head. IIf() returns a
variant data type and may treat the result as text.

I would also recommend changing the "Weekday([CODetails].[Date]) AS Weekday"
to not return a function name as a column heading. Perhaps
Weekday([CODetails].[Date]) AS Wkday
Year, Month, and Date are also function names and shouldn't be used as field
names. You should change them if not too difficult.
--
Duane Hookom
Microsoft Access MVP


menube said:
SELECT
IIf(Weekday([CODetails].[Date]-[PrepAdj])=1,[CODetails].[Date]-[PrepAdj]-2,IIf(Weekday([CODetails].[Date]-[PrepAdj])=7,[CODetails].[Date]-[PrepAdj]-1,[CODetails].[Date]-[PrepAdj]))
AS PrepDate, IIf([List Order]=10,[Vegitarians],[StudentCount]) AS Count2,
Customers.Vegitarians, PackSheetItems.[List order], PSJoinTable.PSItemID,
CustomerOrders.COID, CustomerOrders.CustomerID, CountTable.Division,
CountTable.Subdivison, CountTable.StudentCount, CustomerOrders.MealType,
CustomerOrders.ServingType, CustomerOrders.Month, CustomerOrders.Year,
CODetails.Date, CODetDet.MenuItemID, PackSheetItems.PSIPortion,
PackSheetItems.PSUOM, PackSheetItems.Department,
IIf(Weekday([CODetails].[Date]-[DelAdj])=1,[CODetails].[Date]-[DelAdj]-2,IIf(Weekday([CODetails].[Date]-[DelAdj])=7,[CODetails].[Date]-[DelAdj]-1,[CODetails].[Date]-[DelAdj])) AS DelDate, Weekday([CODetails].[Date]) AS Weekday
FROM (MenuItems INNER JOIN ((Customers INNER JOIN (CustomerOrders INNER JOIN
(CODetails INNER JOIN CountTable ON CODetails.CODetID = CountTable.CODetID)
ON CustomerOrders.COID = CODetails.COID) ON Customers.CustomerID =
CustomerOrders.CustomerID) INNER JOIN CODetDet ON CODetails.CODetID =
CODetDet.CODetID) ON MenuItems.MenuItemID = CODetDet.MenuItemID) INNER JOIN
(PackSheetItems INNER JOIN PSJoinTable ON PackSheetItems.PSItemID =
PSJoinTable.PSItemID) ON MenuItems.MenuItemID = PSJoinTable.MenuItemID
WHERE
(((IIf(Weekday([CODetails].[Date]-[PrepAdj])=1,[CODetails].[Date]-[PrepAdj]-2,IIf(Weekday([CODetails].[Date]-[PrepAdj])=7,[CODetails].[Date]-[PrepAdj]-1,[CODetails].[Date]-[PrepAdj])))=[Forms]![ReportInformation]![Text0]));
When I type the date criteria directly into the query, I get the all the
data for that date. If I ask the query to get the data form the form I get
no data at all. I can cut and past the request for data into a non
calculated field and I get the data for that date.

I hope this is the information that you need. Thanks for you help.
--
menube


Duane Hookom said:
Can you provide the SQL view of the query with the criteria from the form?
Can you tell us what you mean by "it does not work" which tells us almost
nothing?
--
Duane Hookom
Microsoft Access MVP


:

I have a delivery date field in my query that is calculated. When I type the
criteria directly into the query exp. #10/20/08# everything works fine. If I
use a form to retrive the criteria it does not work. The problem only occurs
with the calcualted field. I'm new at this so I'm sure I'm missing
something. Any help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top