result from a query to specify string to use

[omega015]

I have a query that returns the string to use but I am not sure how I can actually use the returned value to do what I want.

e.g.
my table:
Partner ID Backup Field
5023949 UPC
501354 GTIN

my query will return 1 [backup field] value from the table above, and I then want to use that value to get another value that was previously declared in my code.

eg
GTIN = "ABC123"
UPC = "123ABC"

if my query returns the value "GTIN" then need to use the value of GTIN (ABC123) in my next part of the code.

msgbox GTIN would result in a message box ABC123

but im not sure how to get the result value. I can say msgbox [backup field] but that returns "GTIN" not "ABC123"

 

[pbaldy]

Sounds like you need a table with 2 fields, the "backup field" value ("GTIN") and the return value ("ABC123"). You can join that field to the original table, enabling you to return the result value associated with the backup field.

 

[vbaInet]

I think that the omega015 is trying to retrieve the value for use in code. If that's the case then a DLookup() will suffice:

http://access.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[pbaldy]

I'll get out of the way.

 

[vbaInet]

I'll get out of the way.

You're not in anyone's way, and I could be wrong with my assumption

 

[omega015]

Thank you to you both. I am indeed using this value in code. But the value I seek is not in the table but is in the code.
I am reading through some text files and storing data in strings and if a string is empty then I am wanting to use a backup string for the data. But the backup string I use can vary depending on some condition, so I am storing which string to use in the table not the data itself.

The dlookup seems to be another way of what I am doing now with the recordset but I still am struggling to workout how to use the returned value to select the value in the stored string.

let me see if I can copy the relevant code to help explain. And again thank you for your time.

 

[omega015]

........ code code code ...........
backup_option1 = "ABC"
backup_option2 = "123"
backup_option3 = "XYZ"
........... more code more code........

If original_choice = "" Then
my_backup_option = DLookup("[Backup Option]", "mytable", "[Partner ID] = '" & customer & "'")
MyResult = '//here i need to get the value of one of the backup options above based on which option the dlookup said to use

Else
MyResult = original_choice
End If

The backup options that are declared will be different and unknown until I cycle through text files and which one to use will again depend on the partner in the text file.

I could do all this by simply not using a table and adding that into the code but I am trying to keep things simple for others to maintain should they need.

Again thank you for your help.

Michael

 

[jdraw]

I suggest you show readers an example -- as clear and simple as possible -- of what you are trying to do. I'm not clear and I think it stems from your use of the word "code". Code has several meanings which may be the cause of the confusion.

Code as in vba or SQL code.
Code as in the result of a lookup table using a input value to retrieve a code.
Code as some string within the original "text file".

 

[omega015]

ok this should be a really simple bit of vba to show what I am trying to do. apologies for the generic term code.


Function myfunction()

customer = "cust1"
original_choice = ""

backup_option1 = "ABC"
backup_option2 = "123"
backup_option3 = "XYZ"

If original_choice = "" Then
my_backup_option = DLookup("[Backup Option]", "mytable", "[customer] = '" & customer & "'")

MyResult = '//here i need to get the value of one of the backup options above based on which option the dlookup said to use

Else
MyResult = original_choice
End If

End Function

 

[vbaInet]

If you say that your query will only ever return one backup option then the DLookup() without any criteria, will return that value. Just give it the field name and the table name. Please review the links provided for examples.

 

[omega015]

Hi vbaInet,

The dlookup will only return 1 backup option but the criteria is needed to filter down to that 1 record.

using the above function as an example this would be my table:

mytable

[customer], [Backup Option]
cust1 backup_option1
cust2 backup_option3

The dlookup from my example will return the value "backup_option1" as that's what is in the table, however I want to ultimately get to the value "ABC" which is the value of backup_option1 which is defined within the vba code. The table does not contain my data but is a pointer to which string does contain the data to use. I could use a select statement but then I would need to hardcode the cases for all possibly backup_options.

Thanks,

Michael

 

[vbaInet]

Only going by what you stated here:

my query will return 1 [backup field] value from the table above

If your query returns all values then yes you will need to add criteria. Have you had a look at the examples?

 

[jdraw]

For clarity:

Is there only 1 backup option per Customer?
If so, is that backup option unique to that Customer?

 

[omega015]

Sorry for any confusion. Yes there is only 1 backup option per customer, and no the backup option could be the same for multiple customers.

I have looked at the dlookup examples you have given me and incorporated that into my function and this works as expected as it does return the correct value from the table. I have no issues with the dlookup and what is being returned is correct.

My issue is I want to take the result of the dlookup and use that to get a string value declared earlier in the code. If you run through my example function, where I have left MyResult unfinished this is where I want to get MyResult = "ABC"

Thank you for your help.

 

[vbaInet]

You can use a Dictionary object, but to keep things simple, save those values in a two-dimensional array, loop through the array and perform an equality test.

It's probably best to save the corresponding value in your table instead, that way you can just use DLookup() alone. Are there any reasons for not doing it this way?

 

[omega015]

The reason for not wanting to store the values is purely as I didn't need to store any data as I am writing a textfile and once that is written I am done so everything was in memory, and it keeps things simple and quick. I wanted to hold the backup field option in a table so that unskilled users could have a form that would allow them to select the backup field or enter a value as required.

I can just do a couple of if / select statements to achieve what I was trying to do with the table and hope that any changes are few and far between.

Thank you to everyone for your input/help.

 

[vbaInet]

If there are only two values, then yes an IF..ELSE would suffice.