|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 27th, 2008, 03:05 AM
posted to microsoft.public.access.queries
|
|||
|
|||
Criteria from form on calculated field
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. -- menube 
|
|
#2
October 27th, 2008, 04:23 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Criteria from form on calculated field
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 "menube" wrote: 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. -- menube
|
|
#3
October 27th, 2008, 05:43 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Criteria from form on calculated field
On Sun, 26 Oct 2008 20:05:01 -0700, menube
wrote: 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. 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. -- John W. Vinson [MVP]
|
|
#4
October 27th, 2008, 12:08 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Criteria from form on calculated field
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" wrote: 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 "menube" wrote: 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. -- menube
|
|
#5
October 27th, 2008, 01:40 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Criteria from form on calculated field
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" wrote: 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" wrote: 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 "menube" wrote: 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. -- menube
|
|
#6
October 27th, 2008, 08:13 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Criteria from form on calculated field
Thanks I will give it a try.
-- menube "Duane Hookom" wrote: 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" wrote: 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" wrote: 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 "menube" wrote: 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. -- menube
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
