Type Mismatch on Dlookup? Code inside

[Vita]

Dim DhrStatus As String
DhrStatus = DLookup(CStr("StatusCode"), "dbo_JT_WorkTicket" Or "dbo_JT_WorkTicketHistory", "SalesOrderNo" = (CStr(Format(SoNum, "0000000"))) And "WTNumber" = (CStr(Format(DhrNum, "000"))) And "WTStep" = (CStr(Format(StepNum, "000"))))
Debug.Print DhrStatus

Not sure why this is giving me a type mismatch when everything is converted to string? The table its pulling from is also set to be short text.

I think it has something to do with the OR but when I only have one I get an issue of invalid use of null even though there is data in the field StatusCode.

[Welshgasman]

Well Access does not have a message saying 'code is garbage'

First parameter, no need to convert to string, why would you, if you have it as a numeric?
Second parameter, you can only choose ONE domain to look in to. If you need another domain, you issue another DLookUp().
Third parameter is criteria, you need to concatenate any values into the criiteria.

If your status code is in two different tables, that would infer a problem with your structure.

Do yourself a favour and lookup the syntax for commands you do not know how to use. It will save you a lot of time in the future.

https://support.microsoft.com/en-us/...b-bed10dca5937

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the function.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

[Micron]

DLookup syntax is field,table(,criteria). Why would you Cstr the field name?
Format function returns a string, so CStr is redundant there also.
Nor have I ever seen anyone try to use OR in choosing the table name. I doubt you can do that but would be OK with being proved wrong.

Re null:
All it would take to fail is that one record contains null in a field being used in the DLookup and the search comes across it. Suspect your null would be in the SalesOrder field since that is the field with the criteria.

[Vita]

Well Access does not have a message saying 'code is garbage'

First parameter, no need to convert to string, why would you, if you have it as a numeric?
Second parameter, you can only choose ONE domain to look in to. If you need another domain, you issue another DLookUp().
Third parameter is criteria, you need to concatenate any values into the criiteria.

If your status code is in two different tables, that would infer a problem with your structure.

Do yourself a favour and lookup the syntax for commands you do not know how to use. It will save you a lot of time in the future.

https://support.microsoft.com/en-us/...b-bed10dca5937

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the function.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

Hey I'm trying!
I was under the impression that you could have multiple criteria. The people who set this DB up had these 3 fields set to short text even though they could have clearly done numeric so I thought I needed the types to match so that it could pull the data from the field properly.
As for the first parameter being converted haha looking back at it that is just a mistake. The second parameter I googled to see if VBA could handle it having 2 domains and I swear I saw someone commented on another forum saying you could use OR. I don't recall microsoft's info clarifying if you could or not but I generally do go to the info page and read it first. Regardless good to know, thank you, I will just make a second DLookUp().

The third parameter I need multiple criteria as there are 3 different fields it needs to sort by, the order number, the ticket number, and the step number, so that it can check the status of the step.
I do need to change that value if the user chooses (eg: they can hit complete or cancel buttons which respectively mark the order as complete or return the form to empty). I was planning on using this as an opportunity to learn Dao/Recordset.

As for the status code being in two different tables, It is in the Orders table until all steps/tickets are complete then its moved to OrderHistory so I may just write an if statement that will check OrderHistory if the DLookUp returns null.

I attempted to use single quotes but I get an "Expected: expression". It is also Access 2000 so maybe things changed.
Not sure what you mean by date literals? The format function is a bit confusing but I put 0's so that if its blank it will add a 0. Eg: format("123", "0000") Output would be 0123.
The extra 0 is needed due to slight differences in the SQL data and the barcodes being scanned. I know its dumb.

By putting it on a string variable you mean attaching it to something like:

Dim StatusCriteria As String
StatusCriteria = criteria1, criteria2, ...
Debug.Print StatusCriteria

DLookUp(expr, domain, StatusCriteria)

If not please correct me! Edit: I attempted this with taking what I have and putting it in quotes to make it a variable but I get an odd error saying one of the criteria reports SoNum which I think is because its a variable and I'm trying to put it inside a string variable.

DLookup syntax is field,table(,criteria). Why would you Cstr the field name?
Format function returns a string, so CStr is redundant there also.
Nor have I ever seen anyone try to use OR in choosing the table name. I doubt you can do that but would be OK with being proved wrong.

Re null:
All it would take to fail is that one record contains null in a field being used in the DLookup and the search comes across it. Suspect your null would be in the SalesOrder field since that is the field with the criteria.

I was unaware that format returns a string! Thank you for that and I thought about the null thing and mentioned it above!

[Welshgasman]

Yes, of course you can have multiple criteria, but then it is complicated with getting the ANDs or the ORs in the correct place and surrounding various variable with the correct characters.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

I do not think anyting in this regard has changed since ACcess 2000. I myslef still use 2007.

So put you criteria into a string variable, debug.print it until you cannot see any errors, then copy and paste both the code and the output from the debug.print and we can help further.

Remember it is a language. You and I can see a typo a mile off, but the computer just reads what it gets. So you have to be precise and correct every time, not just more or less.
Hell, I have google minis here, where I ask them to switch something ON, and they respond 'switching xxx OFF' !!!!
AI can still not get to right. :-)

You might have read that a DLookup() could return more than one item? Not usually done I must admit, but it is possible. Handy if you are looking for several items of data for the same record in the same table.

[Vita]

Yes, of course you can have multiple criteria, but then it is complicated with getting the ANDs or the ORs in the correct place and surrounding various variable with the correct characters.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything


Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.


Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

I do not think anyting in this regard has changed since ACcess 2000. I myslef still use 2007.

So put you criteria into a string variable, debug.print it until you cannot see any errors, then copy and paste both the code and the output from the debug.print and we can help further.

Remember it is a language. You and I can see a typo a mile off, but the computer just reads what it gets. So you have to be precise and correct every time, not just more or less.
Hell, I have google minis here, where I ask them to switch something ON, and the respond 'switching xxx OFF' !!!!
AI can still not get to right. :-)

You might have read that a DLookup() could return more than one item? Not usually done I must admit, but it is possible. Handy if you are looking for several items of data for the same record in the same table.

I got it working by using the single quotes like you said! I will probably move this to a string variable too. (code below)

Still confused on what you mean by date literals? I'm not using dates.

DhrStatus = Nz(DLookup("StatusCode", "dbo_JT_WorkTicket", _ 
        "SalesOrderNo ='" & (Format(SoNum, "0000000")) & _
        "' And WTNumber ='" & (Format(DhrNum, "000")) & _
        "' And WTStep ='" & (Format(StepNum, "000")) & "'" _
        ), "Order Not Found. Checking History Table.")
                    Debug.Print DhrStatus

[Welshgasman]

You might not be now, but likely will in the future?

[Vita]

You might not be now, but likely will in the future?

Potentially. I will keep it in mind.