VBA code to display previous sale date from the same customer

[Sreemike]

I have a database containing patients with blood test results. The table is called tblDatabase and the relevant fields for this discussion are Firstname, Surname, DOB, and SampleDate. Data entry is done through a form called frmDatabase. Some patients have their tests done repeatedly. While entering patient details on frmDatabase, I fill in the Surname field, then the Firstname field, and then the date of birth (DOB). I'm wondering if it's possible to write a VBA code (possibly using the After Update event) so that a textbox created in frmDatabase displays the sample date of the last test done for the patient in the database. I've been told that DMax might be involved in achieving this. So I want a previous match for surname, firstname and date of birth.

 

[Gasman]

Put all the criteria into a string variable and use that in the function.
That way you can debug.print it until you get it correct.

Application.DMax method (Access)

Office VBA reference topic

learn.microsoft.com

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think? As you are using surnames, I would avoid the single quote method.

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

Numbers do not need anything, does not apply here I believe?


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

 

[Sreemike]

The following works with most dates of birth but not others!!
=DMax("SampleDate","tblDatabase","Firstname=""" & [Firstname] & """ AND DOB=#" & [DOB] & "#")

DMax seems to work in my database with some dob but not on others, such as 01/09/1999, 10/05/1984. This is perplexing. The table looks OK.
Can't figure it out.

 

[Gasman]

Pay attention to the date format I mentioned.

I used to use a constant.

'Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
Public Const strcJetDate = "\#yyyy-mm-dd\#"  'Needed for dates in queries as Access expects USA but will accept ISO format.

 

[LarryE]

displays the sample date of the last test done

Each patient should have each test date in a separate TestDate table anyway. The test dates should not be held in the same table where patient data is kept (tblDatabase).
Then each TestDate table record should hold the results of that test for that day for that patient.
Do you know how to normalize data and create relationships between tables?

It would be best if you would attach a screenshot of your relationships window so we can see how the database project was designed. That will help us help you, much better. It sounds like you are attempting to use ACCESS like you would a spreadsheet. That will not work.

 

[Sreemike]

I am afraid this database is over 15 years old and comprises just one table. There is, thus, no relationships.

 

[too_old]

I am afraid this database is over 15 years old and comprises just one table. There is, thus, no relationships.

so its a spreadsheet ?

 

[LarryE]

I am afraid this database is over 15 years old and comprises just one table. There is, thus, no relationships.

More than likely, this can be converted to a relational model if you wish. I can help you do that.

 

[Sreemike]

so its a spreadsheet ?

 

[Sreemike]

More than likely, this can be converted to a relational model if you wish. I can help you do that.

I may have to find time to re-construct the entire database.
Do you think the single table format is the reason why DMax doesn't work on certain date-of-birth matches?

 

[CJ_London]

the reason why DMax doesn't work on certain date-of-birth matches?

is because sql expects dates declared within a string to a) be surrounded by the # character and b) be in the standard sql format of yyyy-mm-dd or the US format of mm/dd/yyyy or is unambiguous

Your format is dd/mm/yyyy so a date of 19/12/2024 is unambiguous because there are not 19 months in the year. Similarly a date of 5 Jan 2024 is also unambiguous per post #4

However a date of 11/8/2024 is ambiguous in that you see it as 11th august whilst sql sees it as 8th November

In summary you need to provide sql with the correct format per post #4

 

[LarryE]

View attachment 115652

I may have to find time to re-construct the entire database.
Do you think the single table format is the reason why DMax doesn't work on certain date-of-birth matches?

Just as a general rule, if you attempt to use ACCESS or ANY software product in a manner in which it was never designed to be used, you will have nothing but problems. In your case, using DMax (or any other built-in functions) may work, or they may not. DMax can certainly work if you construct the criteria properly. Of course, if you have multiple patients in multiple hospitals with the same DOB, your results could be, well, unreliable.

As other have pointed out, when you are working with dates in ACCESS, it can be very confusing when you are building the criteria part of the function. It would be much, much easier to just design the thing properly from the beginning and you wouldn't need to DMax the latest date for each patient and view it in a text box. All you would need to do is go to that last record in a form. No need to DMax anything.

If you want help in normalizing your data into a relational model, here's what you can do:

1. Copy the database and re-name the new copied file Hospital_Testing.accdb
2. Open Hospital_Testing.accdb
3. Create new fields called PatientNum and ConsultantNum and give each patient and consultant a number so they are all anonymous to outsiders. You can use the Number Data Type or Short Text.
4. Keep the HospitalNumber field but clear all the hospital names in the Hospital field, so we have numbers but not names.
5. Keep all the other fields as they are. They mean nothing to any outsiders.
6. Attach the Hospital_Testing.accdb file by posting a new reply and attaching the file using the Attach files button.

If you don't wish to use outside help, looking at what you have now, after normalization and creating proper relationships, there could be, for example:

1. A Hospital table with multiple hospitals. Each hospital has a Hospital Name, Number and Consultant assignment.
2. A Patients table. Each hospital will have multiple patients. Each patient has a SurName, FirstName and DOB (and any other information you wish to keep).
3. A TestData table. Each patient will have multiple testing data records including a TestDate field and test criteria fields such as NK69 information and Beads information and test result information.
4. The Hospital table is connected with the Patient table with a HospitalID foreign key field
5. The Patient table is connected with the TestData table with a PatientID foreign key field

Once the tables are designed, then import ONLY Hospital information into the hospital table, ONLY patient information into the patient table and ONLY Testing data into the TestData table.

Only after the tables and fields are designed do you make forms, queries and reports.

 

[gemma-the-husky]

If you want to find the largest date from a subset of a table/datasouce then

Dmax(datefield, datasource, filter string ) will select the highest value. This assumes your dates are defined as date fields, and not text strings..

I don't see any circumstances when this will not return the correct date, as long as any date filters in the filter string are consistent with date formatting rules. (Eg US date). In the UK I tend to use "long date".

So for example "between daterange(1) and daterange(2) " needs to comply.

 

[cheekybuddha]

The following works with most dates of birth but not others!!
=DMax("SampleDate","tblDatabase","Firstname=""" & [Firstname] & """ AND DOB=#" & [DOB] & "#")

DMax seems to work in my database with some dob but not on others, such as 01/09/1999, 10/05/1984. This is perplexing. The table looks OK.
Can't figure it out.

If you didn't understand what @Gasman was saying in Post #4 then you should say so.

A small adjustment to your code will work:

=DMax("SampleDate","tblDatabase","Firstname=""" & [Firstname] & """ AND DOB=" & Format([DOB], "\#yyyy\-mm\-dd\#")

 

[LarryE]

Here's an example of what your project looks like in ACCESS with normalization and relationships established. Of course, you can add more fields as you wish to any of the tables. I have attached the file as well.

 

[Sreemike]

I appreciate everyone's help. I want to apologize for not disclosing that I have limited knowledge of both the relational model and VBA. I plan to improve in these areas in the months to come and rebuild the database from scratch. Additionally, I've been advised to stop using the switchboard feature, as Microsoft might remove it in future Access updates.

 

[Sreemike]

Here's an example of what your project looks like in ACCESS with normalization and relationships established. Of course, you can add more fields as you wish to any of the tables. I have attached the file as well.
View attachment 115656

Thank you LarryE. I will go away and understand the relational model in greater depth by doing some courses. I am hoping to do some VBA lessons by Dec. I will construct the database from scratch and may post problems encountered here in the future. I wish you well and thank you.

 

[June7]

I agree you really, really need to modify data structure. Repeating names and DOB is path to insanity (typos are inevitable). And these are poor unique identifiers, even in combination. Your PatientID field serves no purpose.

VBA should not be needed for your requirement. Saving this calculated value into table field should not be done.

Normalize structure then review http://allenbrowne.com/subquery-01.html
Expression in textbox using DMax() could be like:

=DMax("SampleDate","TestData","PatientID=" & [PatientID] & " AND SampleDate<#" & [SampleDate] & "#")

Modify with Format() function if needed.

I just tested this DMax() in textbox and find that it does not immediately recalculate if a record is edited. Form had to be Refreshed (this is where VBA or macro might come into play). Domain Aggregate functions can slow performance of queries and forms.

You could take formal classes that stretch learning out for months or spend a solid week with a good introductory tutorial book (maybe same one used by class). Learn the basics of relational database principles and Access functionality before even thinking about programming. Then learn VBA and/or macro coding. Learn programming concepts that can carry over to any language then learn VBA syntax and vocabulary. Macros are an option and may seem to speed project development but I have never used (with exception of AutoKeys).

 

[Gasman]

I appreciate everyone's help. I want to apologize for not disclosing that I have limited knowledge of both the relational model and VBA. I plan to improve in these areas in the months to come and rebuild the database from scratch. Additionally, I've been advised to stop using the switchboard feature, as Microsoft might remove it in future Access updates.

The switchboard is just a data driven form. Slightly limited, but @Pat Hartman has her own which overcomes the limit of the MS one. She has uploaded it here several times, so look in the Databases forum.

Personally I would not. I was able to have a good switchboard with all the entries attached.
I just added a userlevel for the entries.

DB removed as was a linked table. Correct DB in post #21

 

[Pat Hartman]

Here are several examples. All based on the original switchboard. The database also includes a form you can use to manage the switchboard items table since once you switch to using more than 8 items, you can't use the old maintenance wizard.

P

Thread 'Replacement Switchboard'

Feb 13, 2022

This Switchboard form is based on the last VBA version which I think was A2k. I expanded it to allow for 12 items rather than 8 and you can easily change it to do more. I've included directions. This updated version has two switchboards. One more traditional and the other is big buttons.

THERE IS A NEWER VERSION DOWN AT THE END. IT HAS A MORE VERSIONS OF THE SWITCHBOARD

• Pat Hartman
• Replies: 11
• Forum: Sample Databases

• 

@Gasman Paul, your attached database has only a linked table in it.