updating a list using a date criteria.

[LOUISBUHAGIAR54]

Good day access programmers,

Greetings from Malta.

I wonder if I can please get some help.

I have a form "Appointments" which has a combo box, two list boxes and various other controls. List 54 updates on the 'after update' event of the combo box using code. This is working well

A value on List 54 is referenced from a textbox (Text62) on the form as follows;

=[Forms]![Appointments]![List54].[Column](1).

I had to do this indirectly as the SQL statement using the above statement directly did not work.

List 56 is the second list box. It updates on the 'after update' event of List 54 using VBA code which injects an SQL statement into the rowsource property of list 56.

Another textbox control (Text34) contains a date entry which is entered by the user, while (Text64) is another textbox entering time. Both are formatted as Date/Time entries.

I have added a command button which on click should generate code which among other things injects the following SQL statement so that list56 will update depending the date entry in Text34. I want the rows of the list sorted out according to date and time in that order.

The following is the SQL statement which is injected;

SELECT Query3.Appl, Query3.clName, Query3.Surname, Query3.cltIdcard, Query3.meta, Query3.hin, Query3.cltTel1, Query3.ID, Query3.confirm
FROM Query3
WHERE (((Query3.meta)=[Forms]![Appointments]![Text34]) AND ((Query3.dctidcard)=[Forms]![Appointments]![Text62]))
ORDER BY Query3.meta, Query3.hin;

For clarity sake 'meta' is the date field and 'hin' is the time field in a table.

This SQL statement is not updating the list when the command button is clicked. I suspect that it has to do with how I am writing the SQL statement as it should include something like # # for the date criteria and probably also for the time criteria.

Can someone please enlighten me on this problem ?

LouisB

 

[VilaRestal]

Hi again Louis.

It's the same principle as before: use VBA code to set the rowsource to a string:

Me.List56.RowSource = "SELECT Appl, clName, Surname, cltIdcard, meta, hin, cltTel1, ID, confirm FROM Query3 WHERE meta= #" & Me.Text34 & "# AND dctidcard=""" & Me.List54.Column(1) & """ ORDER BY meta, hin"

I'm guessing dctidcard is a text field. If it's numeric remove the double double quotes from each side.

Also, you may need to format Text34 depending on your regional settings. Access will want it in mm/dd/yyyy format.

And you can get rid of Text62 unless you need it for some other reason.

 

[LOUISBUHAGIAR54]

Hello VilaRestal,


I tried your code and it works well for one particular reference on List 54. On the other rows on the list it does not. Very strange. I will now try to see what is the new stumble.

Thanks for your help once again.

LouisB

 

[LOUISBUHAGIAR54]

I hesitate to ask for help again as it may be something banal which I am missing. I have this list54 which contains names of consultants who will have appointments with patients.

The appointments are shown in another List56. When the name of the consultant is chosen in List54 ALL his appointments with patients show up in List56 with patients' names, date, time and telno etc.

I have a text box Text34, with a date entry. I have a command button and when it is clicked I would like only the appointments for the date shown in Text34 to show up in List56 for the consultant chosen in List54.

I had some trouble initially, but on advise from ViliRestal I included his SQL statement in a vba code on the click event of teh command button. It is now working fine, but for only a single consultant shown in List54. For the others it shows no appointments even when they have such on the date shown in Text34.

I must be missing something very obvious, but try as I may I cannot find what it is !! Does any good soul have any advise ?

LouisB

 

[VilaRestal]

To really nail this we're going to need all the relevant VBA code and the table and field names and data types. Maybe even a sample of data too. (What is special about this consultant's data?)

Uploading the database might be the best way (in Access 2007 format or less if possible) and changing names and addresses (dummy data) to protect the innocent.

 

[LOUISBUHAGIAR54]

Hi ViliRestal,

You seem to get hooked on to this problem like myself. I have never uploaded the database. I think this means sending the database on the forum. Actually I have jus started on compiling this database and all the entries are dummy. So there are none innocent.

Can you explain how to upload the database ?


LouisB.

 

[LOUISBUHAGIAR54]

I am trying to upload the database. You enter onto the switcboard. If one presses Clients one comes up with a list of clients (prospective patients). Data is all dummy. One can enter for edit data on patients here. If one clicks on OP appointments one gets into the "Appointments" form where on finds the ComboBox and Lists I was talking about.

The Combobox contains specialities and List54 contains the respective specialists. If one chooses General Physician in the ComboBox one comes up with three specialists in List54. If one chooses Dr. Louis Buhagiar a whole lists of appointments come up in List56. These can be shortlisted according to a date set in Text 34 (Date field) One can do this by clicking on the command button named "Appts on Date".

There is no shortlisting of data if one presses on the other names.

sorry to land you with this problem but I can't find or think of a solution. The code can be accessed as usual. I hope the database has been uploaded correctly

LouisB

 

[VilaRestal]

To upload, reply to this thread, click Go Advanced and then manage attachments > choose file > upload

 

[LOUISBUHAGIAR54]

Hello,


I'm will be uploading the database now. I thought I have done it already. Here comes...

I hope you have received the file !

LouisB

 

[LOUISBUHAGIAR54]

I am trying to upload the file, but apparently it is too big and more then 2MB. In actual fact the db does not have that much data. Tried to split it but the size remains unchanged. Sorry.

LouisB

 

[VilaRestal]

make a copy, delete any images from form and report designs then compact and repair it.

 

[LOUISBUHAGIAR54]

Hello ViliRestal,


I have now found out what the problem was !! Surprise surprise. Text34 was the date entry control on the form. It was formatted as dd/mm/yy.

When entered as an SQL statement in the way you suggested it was misbehaving very strangely. I suspect that when the dd was in excess of 12 like 24/05/12 it worked fine but otherwise it did not.

I have solved the problem by getting a date variable called zmien
Dim zmien as Date
zmien = Text34

and then doing
zmien = format(zmien, mm/dd/yy).

I then included zmien as a date variable into the SQL statment.

It is now working like clockwork. Many thanks.

Incidentally is there a way one can go about this without entering into the hasle of the date variable. I would like the date on the control of the form to remain like dd/mm/yy. I suppose you will have an answer to that as well. Many thanks for helping out; again.

Now I am learning how to upload Access files. When uploaded are they accessible to all ? My file was not terribly large and yet it had 2.12MB. I removed two pictures from the forms. I always like to include pictures as somehow they help improve the relationship between user an db.

The file size remained 2.12MB after doing that. I suppose I will have to compact and repair. I will do that later.

Many thanks for your valuable insights.


LouisB

 

[VilaRestal]

Access uses mm/dd/yyyy format in its SQL

You can display dates anyway you like and it should follow your regional settings.

However, in queries (behind the scenes) they have to be converted to mm/dd/yyyy (or a single precision number).

Dates are the most awkward field of all. If only it took the Earth 100 rotations to orbit the sun: then we could decimalise it. (And if only the USA didn't have this illogical tens/units/hundreds format. And if only Access didn't use it...)

 

[LOUISBUHAGIAR54]

Cheers.


LouisB