Select Case statement within a DLookup on a form 1

[ralphnyclimo]

I have a small limo company that runs on access. I need help with the syntax for a select case statement on a dlookup of a form. I presently am using a IIf staement and the choice of two options is not enought. I am more than willing to pay a consultant to help me improve the database. Thanks.

 

[hsitz]

Well, there is no case statement structure that can be used inside of a dlookup.

I believe you can nest iif statements to get the same effect, though:

For example:

=dlookup(iif(case1, 1, iif(case2, 2, iif(case3, 3, 0)))

should return 1 if case1 is true, 2 if case2 is true, 3 if case3 is true, and 0 if none are true.

The above method can grow unwieldy fairly quickly if there are more than two or three cases to check, though.
----------------------------------------------

Are you using an iif function because you're using the dlookup to return a field in a query? If so, you can work around the limitation of needing to use iif's in a query by referencing a function that you create and put in a module. For example, type something like the following function into a modfule:

public lookupfunction(insert arguments here) as [datatype]

select case dlookup([parameters for dlookup here, should probably be passed in as arguments to function])

Case 1
'statements to execute if the dlookup returns 1
case 2
'statements to execute if the dlookup returns 2
case 3
'statemetns to execute if the dlookup returns 3
'and so on

end select


Then in place of the dlookup function in the query, simply call the function you put in the module, like this:

=lookupfunction(argument1, argument2, argument3)

where argument1 and 2 and 3 are any arguments that you need to pass to the function to use in the dlookup. Could pass in field name, tablename and where clause of dlookup function, as one way of doing things.

-=---------------------------------------------------

If you're not using the dlookup as part of a query, you can forget about putting the function in a module and just insert the select case structure in whatever procedure you're working in on a form or report.

Sorry if I've confused you, but there are different ways of implementing what you want to do depending on where you're trying to insert this "case" structure. Feel free to post a message for clarifications. . . -- Herb

 

[jebry]

Hi!

We could use a little more information. If you could post the code you are presently using, that would help. If you think it would help you can email me a copy of the database, preferably zipped.

Jeff Bridgham
bridgham@purdue.edu

 

[fredl]

Dlookup() expects you to give it three things inside the brackets.
Look up the_____field, from the_____table, where the record is______
Each of these must be in quotes, seperated by commas.
=Dlookup("CompanyName","Company","CompanyID = 200&quot

or

Dlookup("CompanyName","company","Company = " &[companyID])

remember if it's text you have to put quotes around it.

Hope that helps

 

[hsitz]

If you were using the dlookup function to fill a textbox on a form (as I now notice it sounds like you're doing), you can create a function 'lookupfunction' in the form's module, and then just reference it as described in previous message. No need to put it in a non-form module.

You probably wounldn't even need to pass arguments to the function, then, just hardcode in references to the form's field values in 'lookupfunction'. -- Herb

 

[hsitz]

Sorry, I'm not thinking very well today!

The code below that I suggested is complete gibberish. it doesn't include the tablename or where clause of the dlookup:

=dlookup(iif(case1, 1, iif(case2, 2, iif(case3, 3, 0)))

You could use an iif statement inside a dlookup to specify, for example, which fieldname to return, in the following way:

=dlookup(iif(case1, "[field1]", iif(case2, "[field2]", iif(case3, "[field3]", 0))), "[Tablename]", "[whereclause]&quot

Alternatively, you could use a similar iif structure as part of the where clause or even the table clause. You'll probably find that using the iif's inside a dlookup this way is too messy and cumbersome, but I wanted to correct the misinformation I gave in a previous post. -- Herb