Visual Basic Code for a query - Any Help!

[wally2321]

I am trying to create a query using the Design function of the Make query, but I am running into difficulties. I am trying to put a piece of VB code in the field, but I keep receiving errors or wrong data. The field name I am trying to create is Origin Group. Within this field, I would like to cross-reference a table and only return values that match. The problem is that it has to match in different combinations.

Source Table: FLORENCE_MATERIALS
Cross Reference Table: X-REF VENDORS
Reference Fields: Primary Vendor, Vendor Number, Account Group


Situation is as follows....
Origin Group = if primary vendor (in florence_materials table)= Vendor Number (in X-Ref Table)but only return the value of 300 if account group field (in X-ref table) = 7520. Within the same field should be another possible if statement...If primary vendor (in florence_materials table) = Vendor Number (in X-ref table) but only return the value of 100 if account group field (in X-ref table) = 7510.


PLEASE HELP!!! I am obviously over my head and need some help.

Please let me know if you have any questions

 

[mymou]

Create a function in VB that defines all your logic. This should be located in a module:

Function MyLogic(PrimaryVendor, VendorNumber, AccountGroup)

if PrimaryVendor = VendorNumber then
if AccountGroup = 7520 then MyLogic = 300
endif
'add some more possibilities here (if ...then, select case)
End Function

This function can then be called within a query (like any other function) via the expression builder.

MyLogic([primary vendor],........)

Havent tested this - but hope it points you in the right direction

 

[braindead2]

I think you can do this all via your design grid

add both tables

join the tables by dragging florence materials primaryvendor field and dropping it on the xref vendornumber field this will create a inner join (both fields will need to be equal)

now add the primaryvendor field from the florence table to the grid

then add the vendornumber from then x-ref table to the grid

in the criteria under primaryvendor type 300 on the first row then on the 2nd row Or: type 100

in the criteria under vendornumber type 7500 and the row Or: type 7510

I think this is what you want