Criteria from form on calculated field

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.
 
D

Duane Hookom

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?
 
J

John W. Vinson

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.
 
M

menube

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.
 
D

Duane Hookom

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...
 
M

menube

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Dcount (multiple criteria) 3
Between Dates Criteria 2
Referencing a Caculated Field in another Calculated Field 4
calculated field question 5
Use expression/calculated fields as criteria? 5
Criteria in a calculated field 8
Calculated field help? 2
Aggregate calculations on calculated query fields? 6

Top