General discussion

Locked

Looking up table data in Access 2000 VBA

By gemailhiot ·
I am developing an application, my first one, using Access 2000 and VBA.

I have a table containing all the pay periods for 2001. I need to check a date entered on a form as being in the right pay period.

I have created code for an OnExit Eventat the data entry date field.

the code is as follows:

Dim dbs as Database
Dim rsPay as Recordset

Set dbs = OpenDatabase("x.mdb")
set rsPay = New Recordset
rsPay.Open "SELECT * FROM [Pay Period], DBQ=x.mdb, WHERE (Start_Date <= Combo6 and Stop_Date >= Combo6), adOpenTable

Combo6 refers to the entered date. Date format is the same in the table as in the form - short date mm/dd/yy.

The above code is just one of the last iteration and attempt to get at those records. Every time the system returns the message "Type Mismatch". I find that the VBA help is less than helpful on the topic.

Anyone got a suggestion or another technique for achieving the same results?

thanks

gilles mailhiot

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Looking up table data in Access 2000 VBA

by Peyison In reply to Looking up table data in ...

Hi Gilles,

You need to use string concatenation when creating your Select statement with the combo box. Something like:

rsPay.Open "Select * from [Pay Period] Where Start_Date <= #" & Combo6 & "# and Stop_Date >= #" & Combo6 & "#"

The & is used to concatenate the strings together (the Select text, and the contents of Combo6). Also, the # signs should be used since you're doing date comparisons. This tells Access that the values you are passing are dates. When the concatenation is complete, you should see the full text like:

...Where Start_Date <= #01/05/01# and Stop_Date >= #01/05/01#

Put a breakpoint at that point in the code if you want to examine the select statement you've created.

Also, you might want to try changing your Where clause to be something like:

..."Where #" & Combo6 & "# Between Start_Date and Stop_Date"

Hope this helps.

Collapse -

Looking up table data in Access 2000 VBA

by gemailhiot In reply to Looking up table data in ...

Thanks Michael,

Although I am not sure I understand the reason behind the use of those pound signs, I followed your suggestions, and everything came out fine.

Thanks again

Collapse -

Looking up table data in Access 2000 VBA

by gemailhiot In reply to Looking up table data in ...

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums