Hi all, I am trying to create a calculated field in a query and can't figure it out.
The query is based on tblCustomerOrders and the only three fields I need are OrderNumber (PK Autonum), CustomerNumber and Date_TimeOfOrder.
I'm trying to add a fourth field called 'FirstOrder' to calculate if an OrderNumber is the first order placed by a customer and mark it as Yes/No.
When the field is created/working I want to use it in a form and a report recordsource using Dlookup. In the report there is a check box called 'First Order' and I want it to be ticked if it is a customers first order. In the form I want to use it in the underlying query to group on (for showing products which are part of a first order and which are not).
Here's what I have so far:-
but I know it's not right lol. It just asks me to input 'First' and then marks all Orders as No (in the FirstOrder field).
Here's the sql:-
I know I can change the query to a totals query, remove OrderNumber from the query and group on CustomerNumber, then put 'First' in the total for Date_TimeOfOrder and this gives me the first date for a customer, but then the 'OrderNumber' field is no longer in the query and how can I link it to an OrderNumber in the report and form as required?
Any help would be very much appreciated! Many thanks!
The query is based on tblCustomerOrders and the only three fields I need are OrderNumber (PK Autonum), CustomerNumber and Date_TimeOfOrder.
I'm trying to add a fourth field called 'FirstOrder' to calculate if an OrderNumber is the first order placed by a customer and mark it as Yes/No.
When the field is created/working I want to use it in a form and a report recordsource using Dlookup. In the report there is a check box called 'First Order' and I want it to be ticked if it is a customers first order. In the form I want to use it in the underlying query to group on (for showing products which are part of a first order and which are not).
Here's what I have so far:-
Code:
FirstOrder: IIf([Date_TimeOfOrder]=[First],"Yes","No")
Here's the sql:-
Code:
SELECT tblCustomerOrders.OrderNumber, tblCustomerOrders.CustomerNumber, tblCustomerOrders.Date_TimeOfOrder, IIf([Date_TimeOfOrder]=[First],"Yes","No") AS FirstOrder
FROM tblCustomerOrders
ORDER BY tblCustomerOrders.CustomerNumber;
I know I can change the query to a totals query, remove OrderNumber from the query and group on CustomerNumber, then put 'First' in the total for Date_TimeOfOrder and this gives me the first date for a customer, but then the 'OrderNumber' field is no longer in the query and how can I link it to an OrderNumber in the report and form as required?
Any help would be very much appreciated! Many thanks!