Hi All,
Thanks for popping on to have a read, and hopefully provide me with some help. I've been learning as I go with Access and VBA so apologies for the elementary proficiency, but I'm here to learn !
I believe I need to build a query (open to suggestions, hence the believe) that will only return records that fit within the values entered. The complication is that I need the value entered to be used in a calculation to return the the records. To explain further;
I am building a CRM for an advertising company. The company sells advertising space in specific sizes such as A1, A2, A3 etc. Each piece of inventory (piece of advertising space) is allocated a size, A1, A2, A3 etc. When a sales representative makes a sale they can make a sale which is smaller then the size of the Inventory e.g;
Sales Rep B has sold 4 A5 ads to go in Location X in London these are to be split over 4 A3 frames.
As Sales Rep B has sold 4 A5 into 4 A3 there is additional space left over for sale, this additional space needs to be factored in to whether a record is returned or not.
In drawing up an Invoice Sales Rep B is in the process of selecting inventory. The Invoice form has a sub-tab with a sub field in it called Inventory Invoice, this is a sub field as an Invoice can have more than one piece of Inventory.
In the Inventory Invoice sub field there are the following fields;
InvoiceInventoryID - Unique Identifier
IISize - Combo box bound column being size in Inches (this ensures the size is left as a value in the field and not a Key Number)
IIStartDate - The date advertising is to start from
IIEndDate - The date advertising is to end from.
IILocation - The location the Ad is to be placed in.
InventoryID - This should only allow inventory to be selected that is not booked in-between Start and End Dates, and also has enough space left over to fit the ad.
InvoiceID - This is the secondary key for the Invoice table.
To determine whether the Inventory has enough space left the query, or code, will have to perform a calculation. It will have to select the value from from one table and take it away from a value in the inventory table, if the result fits the size entered in the Invoice Inventory table then the user can select the piece of inventory for the booking, however if the ad does not have this available it should display a message box stating this inventory is not available.
It will have to search it's own table for bookings associated to the piece of inventory, pull out the integer and take it away from the IISize (Integer) entered by the user.
To put it into a more logical script this is how I would describe it;
If
IISize = > InventorySize - IISize
And
StartDate To EndDate Not Between StartDate To EndDate
Then Allow Return InventoryID
EndIF
IF
IISize = > InventorySize - IISize
And
StartDate To EndDate Between StartDate To EndDate
Then Do Not Return InventoryID
EndIf
EndSub
You'll have to excuse me with the above, I'm very new to VBA and am not familiar with coding I'm just attempting to write it as logically as possible. Really really need some help so it would be HUGELY APPRECIATED .
Reply With Quote
.
