Help Using Variable in DLookup Statement

[bcmarshall]

I am doing something not normally associated with Access. I'm writing an analytical blackjack program. Anyone who has played the game is probably familiar with a basic strategy chart. It has the ten possible dealer up-card values across the top, and the twenty-odd possible player hands along the left margin. Where the column and row intersect one finds the best way to play that particular combination, be it H(it), S(tand), SP(lit), or D(ouble).



I have recreated that basic strategy chart in a table, with eleven fields in it. The first is Hand, and the rest are the numbers 2-10 and A for Ace representing the dealer up card.

The Hand field has a separate record for each possible player hand, and as one progresses across where it intersects with the dealer's upcard value the appropriate move is stored.

I want to introduce a variable into a DLookup statement so that I can look up the value which corresponds to an up card of say, 2, for example, and a player total of 12. To do that with a standard DLookup statement is quite simple. DLookup("[2]","[Strategy]","[Hand]="12") (My lookup table is called Strategy.)

The problem is that I want to be able to pull the value of the dealer up card (2, in this case) from a form control or other source as a variable, so that if the next hand the dealer's up card is an 8, the statement would begin with DLookup("[8]"...

The Where clause is already ready to accept a variable, so that's not an issue, but in virtually all applications the table field to be searched is fixed, and that's where I want to introduce a variable.

I tried something that almost works. I attempted to concatenate the DLookup expression as text, using a variable where I indicated. The concatenation looks like this.

"DLookup("& """["&Forms!Play!Decks&"]"&""","""&"[Strategy]"&""")

When preceded by the Print command in the Immediate Window, it returns the following: DLookup("[7]","[Strategy]") I haven't bothered with the Where clause yet and won't unless I can get the basic expression to work.

The [7] is a variable brought into the expression from a test form, and the syntax comes out exactly right, but it doesn't actually evaluate the expression. It displays it as the concatenated text string above instead. No combination that I could find of equal signs or other means displayed the actual lookup result by evaluating the concatenation.

This was just something I tried to see if I could make it work, but it appears to only partially work, at least bringing in the value from the outside field. And while the Immediate Window results are encouraging, I can't utilize the same expression anywhere else in Access. It displays a "Can not Parse" message when I try.

All this is a long way around to get back to the basic point. I want to be able to evaluate a player hand vs. a dealer up card, and let the computer choose the appropriate response from the table, and so far I'm unable to do that. Does anyone have any ideas how to a) introduce a variable into a DLookup statement, or b) to accomplish what I'm trying to do by another means?

All help would be appreciated.

[pbaldy]

Perhaps I'm misunderstanding. I just tested this and it worked fine as the control source of a textbox:

=DLookUp([Forms]![FormName].[TextboxName],"GrandTotalQuery")

[bcmarshall]

I've been so conditioned to believe it wouldn't work that I didn't even give it a go. I tried it as you suggested, and unfortunately it didn't do what I wanted it to do.

It returned the value of what's in the form, not the value of what's in the table. Please double-check it and see if you don't arrive at the same results.

[pbaldy]

It looked in the specified query and returned the value in the field whose name appeared in the textbox.

[bcmarshall]

Correct. It returned the value of the field, but not the value in the table.

In my case, my table values consist of H, S, SP, and D. If I look up a 7 without a Where clause, I should get the very first value in the table in the 7 field, in my case an S. When I evaluate the expression shown, it returns the value in the form control, a 7. If you put the exact same syntax into the Immediate Window as [Forms]![FormName].[TextboxName], you'll get exactly the same return as you get when you put in =DLookUp([Forms]![FormName].[TextboxName],"GrandTotalQuery")

Please check to see whether that is your finding as well.

[pbaldy]

It pulled a value from a query that did not exist on the form, and it pulled the field specified in the textbox. I just ran another test in another db, with the same result (in the second test, the form was not bound to the table and thus the value could not have come from the form):

?dlookup(forms!frmMainMenu.text8,"table1")

I'm testing without a where clause, but it is pulling from the table, not the form. Perhaps you can post a sample db that demonstrates what you're trying to do?

[bcmarshall]

Didn't see your reply. Sorry.

When I put in the syntax you included dlookup(forms!frmMainMenu.text8,"table1"), I get the exact same value that I get when I put in forms!frmMainMenu.text8. I even changed the table name and got the same result.

I included a small sample with the table in question and a form with a test field on it. If you open the Immediate Window you should see what I've seen. It will pull the value from the form, not the value from the table. I'll be interested to hear your results.

[pbaldy]

Test with one of the alpha field names, "hand" or "A". The problem is with the numeric field names. I think Access is getting confused about what you want. It will also work if you enter a bracketed field name in the textbox, like:

[3]

It also works with:

?dlookup("[" & forms!test.test & "]","strategy")

[bcmarshall]

Thanks so much! I can't believe it was that straightforward. I didn't notice the problem with alpha vs. numeric. I had one number in the test field and didn't bother changing it. Thanks also for noticing that I can add brackets to the content and make a numeric value work straight from the control.

It makes me feel good to know I confused Access. It has confused me on many occasions!

Seriously though, you've solved my problem and allowed me to move forward with this. I never realized that a simple name like a number could confuse the Mighty Access. Sometimes the most obvious things can be the most difficult to spot.

It should be an interesting program once it's operational.

[pbaldy]

Glad we got it sorted out.