Dlookup problem

[Bradders]

Firstly new to this forum and after a long time of not using access I have decided to use the program for a customer database. I am currently creating tables and forms for different aspects of our business. Unfortunately I seem to be banging my head head against a wall on this one and I am hoping someone would be kind enough to help me out.

Here goes....

I have a table with a date field which is to remind us to contact the customer. What I am trying to do is to create a pop up based on a date within that table. I have managed to do this successfuly using the following VBA on a welcome form:-

Private Sub Form_Timer()

Dim ID As Long

ID = Nz(DLookup("RecordID", "IntruderInstallationT", "FollowupDate<=#" & Now & "#"), 0)
If ID <> 0 Then
DoCmd.OpenForm "NoticeF"
End If

End Sub

However I have a problem, if the date in the field is greater than 30 days the NoticeF displays without a hitch, but if the date is less than 30 days, nothing works it just sits there no error messages but as soon as I change the date back to more than 30 days prior the PC time it works again.

Sorry long winded I know, but thanks in advance.

 

[The_Doc_Man]

I might have done the lookup differently. I might have just recovered the FollowupDate into a date variable and then do the comparison to NOW() in a second step. There is also this: That DLookup looks suspiciously like it doesn't have a customer qualification to go with the date qualification. I.e. how do you know that the RecordID belongs to the given customer (if more than one customer's info is in IntruderInstallationT)?

Not saying you have it wrong, but because there is so much we don't see behind the scenes here, it is hard to understand the example well enough to offer specifics.

 

[June7]

If FollowupDate is <= Now, it is already past due. Don't you want to be forewarned about upcoming followup dates?

And as Doc points out, without customer criteria, DLookup will return first RecordID it encounters that meets date criteria, most likely the same one every time code runs.

Is form supposed to open to a specific record?

Now() includes time component. Use Date() for just date component.

 

[Bradders]

I might have done the lookup differently. I might have just recovered the FollowupDate into a date variable and then do the comparison to NOW() in a second step. There is also this: That DLookup looks suspiciously like it doesn't have a customer qualification to go with the date qualification. I.e. how do you know that the RecordID belongs to the given customer (if more than one customer's info is in IntruderInstallationT)?

Not saying you have it wrong, but because there is so much we don't see behind the scenes here, it is hard to understand the example well enough to offer specifics.

Hi The_Doc_Man

Thank you for you reply

The Record ID we manually enter to link to another table what I have basically done is a customer record table then a seperate table for maintenance contracts all I wanted to do is when the date is today or in the past it will open a form to say we need to contact them. I have linked all the tables with relationships which seems to work ok. I have not entered any data yet so can change things without casuing too much damage.

I am not that confident with all the VBA so any suggestions would be great.

 

[June7]

You could provide db for analysis. Follow instructions at bottom of my post.

 

[Bradders]

If FollowupDate is <= Now, it is already past due. Don't you want to be forewarned about upcoming followup dates?

And as Doc points out, without customer criteria, DLookup will return first RecordID it encounters that meets date criteria, most likely the same one every time code runs.

Is form supposed to open to a specific record?

Now() includes time component. Use Date() for just date component.

Hi Thanks for that, I did try the date format and the exact same thing happens. What I have done is when a date is today or past (dont want future) it opens a blank form to say that they need to be contacted then I have put a button on that form to go to the records that match that criteria (serperate table) with basic info on.

Just a quick thought though when I use Date() after saving the () dissappear, is this normal?

 

[CJ_London]

Depends on where you are in the world but unless you use the US date format , you need to use the format function to convert to the US style of mm/dd/yyyy or use the sql standard of yyyy-mm-dd as a string. As it is if today is 2nd March that will be treated as 3rd Feb

however in this case since now is a function recognised by access sql you could use

"FollowupDate<=Now()"

Which means you will be comparing with a date datatype rather than a string to be treated as a date - indicated by the use of the # character

 

[June7]

Yes, VBA recognizes Date same as Date() and automatically drops (). Query needs and will retain (). Same with Now().

I didn't expect change would solve your issue but using Now() can sometimes cause wrong output. Just be aware of the situation and make sure you use most appropriate.

 

[Bradders]

You could provide db for analysis. Follow instructions at bottom of my post.

Depends on where you are in the world but unless you use the US date format , you need to use the format function to convert to the US style of mm/dd/yyyy or use the sql standard of yyyy-mm-dd as a string. As it is if today is 2nd March that will be treated as 3rd Feb

however in this case since now is a function recognised by access sql you could use

Which means you will be comparing with a date datatype rather than a string to be treated as a date - indicated by the use of the # character

Hi CJ,

I'm in the uk so will be using dd/,mm/yyyy I will try changing the #character

So now I have

Depends on where you are in the world but unless you use the US date format , you need to use the format function to convert to the US style of mm/dd/yyyy or use the sql standard of yyyy-mm-dd as a string. As it is if today is 2nd March that will be treated as 3rd Feb

however in this case since now is a function recognised by access sql you could use

Which means you will be comparing with a date datatype rather than a string to be treated as a date - indicated by the use of the # character

I will give this a bash in the morning perhaps with fresher eyes, will let you know how I get on.

 

[June7]

Review http://allenbrowne.com/ser-36.html

 

[Pat Hartman]

Welcome to AWF.

There are several issues.
1. This is not a task for a timer event. You want this event to run when the application opens. I'm not clear on what the rules are aside from the date but we can figure that out.
2. As the others have mentioned. Now() includes time and will probably not work the way you anticipate. This is a case for using Date(). However, you need to check the other logic in your application to make sure that you are not using Now() when you should be using Date() in other places. If it turns out that the table already has dates with a time component, you need to fix the code to swap out Now() for Date() and then create an update query to get rid of the time component. We can help if you need us to.
3. By your logic, you would only get the message to contact this particular client if you opened the client's record. That is probably not what you actually want.

So, think again about what the rules are for contacting a client and tell us in words. In addition to the date, is there anything else? How will you stop the app from telling multiple users to contact the client? Do you log contacts? Then we'll help you with the query and where/when it should run.

Regarding dates - There is a lot written on this issue so I'll be brief. Dates are stored internally as double precision numbers NOT strings. SQL "assumes" that ambiguous string dates are in US date format - so mm/dd/yyyy. When you are working with a date that is a string as you are in the code you posted, you MUST format the date as mm/dd/yyyy or if you prefer, the unambiguous yyyy/mm/dd. This has NOTHING to do with how a date is stored or displayed. Also, you did not have to convert the date to a string at all.

ID = Nz(DLookup("RecordID", "IntruderInstallationT", "FollowupDate <= Date()"), 0)

The syntax above leaves the date as an object. Therefore, the query engine will use its natural double precision value without any formatting by comparing the FollowupDate object to the Date() object rather than comparing two strings. Once you convert a date to a string, all operations act like strings so #02/03/2024# (assumed to be Feb 3 rather than Mar 2) is less than #29/02/2024# because 0 is < 2.

 

[ebs17]

The treatment of an individual customer irritates me. When it comes to a customer database, there are certainly several to consider.

I see it as practical to show a form that shows all the customers that need to be contacted. The RecordSource for this is about:

SELECT * FROM Query WHERE FollowUpdate <= Date()

This form could be opened when starting the application so that the user has an overview of what tasks are pending for all customers. An empty form is also helpful information for the user.

The form could then also be opened separately in order to get a new overview after some of the tasks have been processed.

Note: A database has its strengths in mass data processing => doing EVERYTHING at once. Getting unnecessarily caught up in individual calls is just a hindrance and prone to errors.

 

[Bradders]

Hi All, I would just like to say you have all been super helpful so thank you. I changed some of the code to include date and got rid of the # character and all was working. After working late on it last night I forgot to save the code and it went to earlier save version of this :-


Private Sub Form_Timer()

Dim ID As Long

ID = Nz(DLookup("RecordID", "IntruderInstallationT", "FollowupDate<=#" & Now & "#"), 0)
If ID <> 0 Then
DoCmd.OpenForm "NoticeF"
End If

End Sub

And for some reason it is now working which has completely left me confused. I am going to change the now() for date() as I do not need the time format of it.

I am going to have another go make sure it all works

 

[Bradders]

Welcome to AWF.

There are several issues.
1. This is not a task for a timer event. You want this event to run when the application opens. I'm not clear on what the rules are aside from the date but we can figure that out.
2. As the others have mentioned. Now() includes time and will probably not work the way you anticipate. This is a case for using Date(). However, you need to check the other logic in your application to make sure that you are not using Now() when you should be using Date() in other places. If it turns out that the table already has dates with a time component, you need to fix the code to swap out Now() for Date() and then create an update query to get rid of the time component. We can help if you need us to.
3. By your logic, you would only get the message to contact this particular client if you opened the client's record. That is probably not what you actually want.

So, think again about what the rules are for contacting a client and tell us in words. In addition to the date, is there anything else? How will you stop the app from telling multiple users to contact the client? Do you log contacts? Then we'll help you with the query and where/when it should run.

Regarding dates - There is a lot written on this issue so I'll be brief. Dates are stored internally as double precision numbers NOT strings. SQL "assumes" that ambiguous string dates are in US date format - so mm/dd/yyyy. When you are working with a date that is a string as you are in the code you posted, you MUST format the date as mm/dd/yyyy or if you prefer, the unambiguous yyyy/mm/dd. This has NOTHING to do with how a date is stored or displayed. Also, you did not have to convert the date to a string at all.

ID = Nz(DLookup("RecordID", "IntruderInstallationT", "FollowupDate <= Date()"), 0)

The syntax above leaves the date as an object. Therefore, the query engine will use its natural double precision value without any formatting by comparing the FollowupDate object to the Date() object rather than comparing two strings. Once you convert a date to a string, all operations act like strings so #02/03/2024# (assumed to be Feb 3 rather than Mar 2) is less than #29/02/2024# because 0 is < 2.

Thanks Pat, Think I get you, what I have done whether right or not I do not know. Is when I open the database a welcome form opens first with which I plan to add buttons to. Then a timer event occurs to check the followupdate criteria if some match then it opens a Notice form which I have added a button to to open all the customers that need a follow up so we can deal with them all at one stage.

I will definately try your code suggestion thank you

 

[CJ_London]

And for some reason it is now working which has completely left me confused.

Still using # - Check your dates - today is the 3rd March so 3/3/2024 - which coincides with the same as the US format. I’ll guarantee that tomorrow-4th March - you will have a problem because that will be interpreted as 3rd April

when a date is unambiguous- e.g. 13th March, it will work because there are not 13 months in a year so the date will be interpreted correctly

 

[Pat Hartman]

Then a timer event occurs to check the followupdate criteria if some match then it opens a Notice form which I have added a button to to open all the customers that need a follow up so we can deal with them all at one stage.

There is no need for a timer event. Do not use timers unless you absolutely need to. Add the If statement to the Open event of the menu. If there are people to contact, it will open the Contact form automatically. Otherwise, the menu will just open showing the buttons.

If you have multiple people running the application, the Contact form will open for each person. Is that going to be a problem? On the Contact form, you might want to add a button to say "I'll call this one" and update the client record with the user who is calling him. Then you refresh the form showing the contact is in the process of being contacted. You might also need code when the client record opens to warn people to not call. You need to think about how the group works but more importantly, you don't want to ever bug the customer. I can go into this in more detail if you need it. I use the concept in one of my apps. You also need code to unlock the record, Perhaps the form will have a control where you enter comments on the call. Saving the record with comments, could automatically "unlock" the record. Also, there will be times when a record gets locked and needs to be manually unlocked so you need an Admin function to do that.

Also, did you understand the problem with your DLookup() and fix it?

 

[Bradders]

Still using # - Check your dates - today is the 3rd March so 3/3/2024 - which coincides with the same as the US format. I’ll guarantee that tomorrow-4th March - you will have a problem because that will be interpreted as 3rd April

when a date is unambiguous- e.g. 13th March, it will work because there are not 13 months in a year so the date will be interpreted correctly

I know I am going to change it, forgot to save it. Completely forgot the date was 3/3/2024 no wonder it worked (LOL) I will change it again without the #character, I am only working on this in my spare time so will try again later.

 

[Bradders]

There is no need for a timer event. Do not use timers unless you absolutely need to. Add the If statement to the Open event of the menu. If there are people to contact, it will open the Contact form automatically. Otherwise, the menu will just open showing the buttons.

If you have multiple people running the application, the Contact form will open for each person. Is that going to be a problem? On the Contact form, you might want to add a button to say "I'll call this one" and update the client record with the user who is calling him. Then you refresh the form showing the contact is in the process of being contacted. You might also need code when the client record opens to warn people to not call. You need to think about how the group works but more importantly, you don't want to ever bug the customer. I can go into this in more detail if you need it. I use the concept in one of my apps. You also need code to unlock the record, Perhaps the form will have a control where you enter comments on the call. Saving the record with comments, could automatically "unlock" the record. Also, there will be times when a record gets locked and needs to be manually unlocked so you need an Admin function to do that.

Also, did you understand the problem with your DLookup() and fix it?

Thanks Pat, I will get rid of the timer even and do it on open form. We do not have multiple people running the application as only a very small business.

If you all dont mind I will rewrite it in open form, copy the code and see what you all think. We will not overbug the customer as we will do lots manually.

I may upload the database so you can see what I am trying to do.

 

[Solo712]

The treatment of an individual customer irritates me. When it comes to a customer database, there are certainly several to consider.

I see it as practical to show a form that shows all the customers that need to be contacted. The RecordSource for this is about:

SELECT * FROM Query WHERE FollowUpdate <= Date()

This form could be opened when starting the application so that the user has an overview of what tasks are pending for all customers. An empty form is also helpful information for the user.

The form could then also be opened separately in order to get a new overview after some of the tasks have been processed.

Note: A database has its strengths in mass data processing => doing EVERYTHING at once. Getting unnecessarily caught up in individual calls is just a hindrance and prone to errors.

I am surprised to see you as the only one who sees the obvious here (beyond the date format and so on). The Dlookup will hit the first record that matches the criterion, so obviously as a first thing you would want to see how many followups are due at any particular date. So yes, a select query is in order. The other issue is the mysterious "RecordID" field. What is that? The table in the aggregate function name certainly does not suggest it is a customer table. This of course raises the probability that a particular customer has one-to-many relationship with the followups. In that case the DLookup is completely useless. Most likely the "NoticeF" form should be opened if a DCOUNT > 0, with a combo box to select the followups. But again, we really need to see the data structure to get the sense of the problem.

Best,
Jiri

 

[Pat Hartman]

I am surprised to see you as the only one who sees the obvious here

I guess you didn't understand my suggestion where I mentioned a form that selected ALL the customers who needed contacting and then suggested a task management process where people could identify the person they were contacting so that if multiple people were working on the calls, they wouldn't all be calling the same customer.