Forms - Dlookup function

when using Dlookup function, how can i give 2 criteria?
im assuming the following is wrong as its throwing up an error

mesc2 = DLookup("MESC", "MescList", "ComponentID='" & [tempId] & "'", "Size='" & [tempSize] & "'")

in my query when i'm searching for MESC value in the MescList table, i need to specify 2 criteria- componentId and Size (as each component Id can come in several sizes).

how can i do this please!

You CAN have multiple criteria, the trick is in the syntax.
So what you need is:

Thanks don! that worked ...

that made me wonder, i have pretty decent knowledge of sql.. can i use sql codin instead of vb somehow?

I don't think you can use sql for dlookup. If you want to use sql, you may consider using ADO or DOA recordset instead.

There is occasionally an overlap, but they are not at all the same kind of languages. SQL is strictly a database query language that is interpreted by the database engine. It can't do anything outside of the database. VB and VBA are general purpose computer languages that can do all sorts of things, and can have loops and branches, deal with regular files, etc. The big advantage of SQL is that virtually every modern database uses a form of it, although there are slight syntax differences from one engine to another. So you can often use the same SQL code (or just slightly modified) that you wrote for Access, with SQL Server, Oracle, Informix, postgress, MySQL, and on and on. In fact, VBA has to use SQL to get data from Access, even when it wraps it in a function like DLookup().

that makes things very clear

you gentlemen are helping me out big time in figuring out Access!

Another query. My next project is designing a student db for my dad's animation training institute. I believe Access can not be used to create a DB which can be deployed on a LAN network. Can it?

I figured I will use J2EE and oracle, but as they don't have any full time technical ppl working there, Access would be better for matainance purposes.

I got stuck with DLookup fn again!

problem is when i try to use it for strings with spaces in between.

mesc1 = DLookup("CommonMesc", "PipingComponents", "ComponentType='" & [temptype] & "'")

The above code works fine when "tempType" contains a single word. But if ComponentType="blind flange" say, it doesn't.
So i tried to put extra quotes:

mesc1 = DLookup("CommonMesc", "11440_Components", "ComponentType=""" & [tempType] & """")

lol so now it works when ComponentType contains 2 words but not more than that!!
i have some components whose names contains 4/5 words. what can i do here please!?

Could I offer a suggestion. If you are looking up by a text description field. I would change that so that it's looking up by the unique identifier for your table. So in your componenttype table you should have some sort of unique identifier. Use THAT field do perform your lookup not the part description.

Access CAN be used for a small number of simultaneous users over a LAN, but there are some limitations. Usually it will be necessary to split the database into two .mdb files, one of which holds all the tables and nothing else, and is located on a shared directory someplace on the LAN, the other .mdb contains everything else (queries, forms, reports, etc.) and links to the tables (File menu, Get External Data, Linked Tables). Each user will have their own copy of the second .mdb located on their work station. Thus, the data all exists in one place and is linked by each of the running applications. This usually works acceptably if no more than 4 or 5 users are likely to be actively using the database simultaneously, and if the network isn't really slow, due to overload from other activity. It does require that each work station has a copy of MS Access installed.

If your operations don't meet those conditions, the next option to consider would be to install MS SQL Server (the Express edition is freeware), then you can use Access as the front-end, much like I described above. This would save you developing a complete application in J2EE or other programming language. Take a look at http://www.microsoft.com/sql/editio...ss/default.mspx

Ordinarily, DLookup() is intended to look up data based on a unique value, such as an ID. The documentation states: Thus, it is probably a bad idea to use criteria that are general descriptions, because if there are duplicates, you will only see the first one. In this case, you probably need to use a Query and provide for the possibility that more than one record will match the criteria.

Thanks for your replies everyone... I guess i'll do all my searches through Id feild to avoid complications.

Don, I'll keep your suggestions in mind when i start that next project!