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.