Dlookup function is not working

[nonakag]

Took me a couple of hours trial and error but finally got the third criteria Syntax to work:

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And DueODue >= Date() and DueODue <= Date()+30 And Completed = False").

I'm guessing that the table field (Calculated Date) DueODue does not need any &, #, or " before it. Learning this the painful way. Thank you again.

 

[CJ_London]

Today is an excel function. In access you use Date

however your dlookup will return the first record it finds at random of the records between the two dates. Suggest use dmax or dmin if you want the latest or the earliest

 

[namliam]

Took me a couple of hours trial and error but finally got the third criteria Syntax to work:

Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And DueODue >= Date() and DueODue <= Date()+30 And Completed = False").

I'm guessing that the table field (Calculated Date) DueODue does not need any &, #, or " before it. Learning this the painful way. Thank you again.

"the table field" ... if it is a field in a table... how is it calculated? Fields in tables are stored information, never calculated on the spot ?

 

[nonakag]

In table design you have the option to choose the field type and one of the options is calculated field. It uses other fields in the table to calculate on the spot.

 

[nonakag]

CJ_London, Thank you. I'm using the information just as a general Dashboard. The table is one to many of the same CR#'s. there are four inspections per CR# and i only need to find one overdue inspection or one inspection due within the next 30-days. i will use conditional formatting to highlight the dashboard with Red for overdue and Yellow for upcoming inspections within 30-days. Does not need to be the latest date just a date for one of the four CR#'s inspections to fit the criteria.

However, I do need help on another issue. I need some sort of a Flag on the Dashboard telling the user that a vehicle is not available to be dispatched. I'm using two check boxes that are in a table called DispatchLog. One Check Box value is for Dispatched and another for Turned-In. If the Dispatcher is keeping up with making sure these check boxes are checked each time vehicle is dispatched or returned, then I can use these two field to determine if the vehicle is dispatched or available on the Dashboard. However, my dilemma is trying to find the correct D' function and enough criteria to single out the specific record to compare the check boxes. There can be many Rows of same CR# future reservations with both check boxes not checked yet, but only one that should be only checked for dispatched for the current user using the vehicle, but will subsequently have both checked for Dispatched and Turned-In, when the vehicle returns. Therefore, many reservations to one CR#, but a Unique Reservation Control Number (Auto Number) for each reservation. I need help on how to use the D' functions with criteria for the two check boxes, Dates of reservation, and the reservation control number to make sure the D' function is comparing the same record of the two check boxes.

Could you helpl? Thank you.

 

[namliam]

Wonderfull new feature by MS that is another example of a bad habit, calculated values belong on query level, form or report level NOT in tables

 

[CJ_London]

describing your data rather than providing example data and what that example data should produce as a result is very frustrating from a responders perspective. It takes you a couple of minutes to describe it, perhaps 5 minutes to provide some example data. It takes responders 10 minutes to work out your requirement from a description, and only a minute from the example data. So unless you can provide some examples covering all eventualities, regret I cannot help.

 

[nonakag]

describing your data rather than providing example data and what that example data should produce as a result is very frustrating from a responders perspective. It takes you a couple of minutes to describe it, perhaps 5 minutes to provide some example data. It takes responders 10 minutes to work out your requirement from a description, and only a minute from the example data. So unless you can provide some examples covering all eventualities, regret I cannot help.

CJ, attached the table data I need to use to find the highlighted row. thank you.

 

[CJ_London]

and what is the basis of that row? i.e. what is the formula you would use to identify it? Simply that CR#=CR01 and VcolnChk is false? Or does the date have something to do with it?

 

[nonakag]

and what is the basis of that row? i.e. what is the formula you would use to identify it? Simply that CR#=CR01 and VcolnChk is false? Or does the date have something to do with it?

CR#=CR01, VcoDispChk = True and VcoInchk = False. If a DLookup can find this instance of a single row, that will suffice to use Conditional Formatting to color in a Flag. Thank you.

 

[namliam]

COnditional formatting is normaly done on the current record, why use a dlookup?

Should be easy enough though to make a dlookup

dim x as string, y as string
x = "CR01" 
msgbox dlookup("YourReturnField", "YourTable", " [CR#]=""" & x & """ and  VcoDispChk = True and VcoInchk = False "

Word to the wize, using "bad characters" like # is on average a bad habit that you want to get rid of.

 

[nonakag]

CJ, Thanks, you got me to follow the right track I just needed to experiment finding the right Syntax for DLookup. I found this works very well:

DLookup("Val([VcoDispChk]+[VcoInChk])","Reservations", "Reservations.].[CR#_] = 'CR01' And [VcoDispChk] = True and [VcoInChk] =False")

I did not know you could Return an Expression of two Fields with DLookup, but it works and finds and calculates if the Boolean Fields add up to 0, -1, or -2. This makes the next step Conditional Formatting very easy. Thanks Again, for putting me on the right track!

 

[nonakag]

Thanks for your solution as well namliam!

 

[namliam]

still DLookup for conditional formatting feels like the excel way of using a vlookup, which is just plain wrong.

AND why on EARTH would you want to add two values that are part of your where clause.... the sum of the two values should only return 1 value....
This seems redonculous!

 

[nonakag]

You are correct namliam. I'm just a novice Access user trying to learn the disciplines as I go along. I will remember your teachings well in my next Access adventure. Thank you, again!

 

[onur_can]

I tried this:
IsNull( Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False"))

still same #error.

IsNull( Dlookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And Completed = False"))

Nz(DLookup("DueODue", "Fleet_PMCS", "[CR#] = 'CR50' And [Completed] = False"))

must be such

 

[CJ_London]

are you using the right table?- the one you provided as an example for this exercise is called reservations and does not have any of the fields you are using in dlookup

 

[nonakag]

Thank you CJ. My apologies. You solved my first issue and I moved to a second Dlookup issue. The first issue was with Inspection Dates. The second issue was with boolean values. I should have kept them separated. Thank you.