Solved

Data Type Mismatch using Calendar Control

Posted on 2009-04-01
12
293 Views
Last Modified: 2012-05-06
I am stumped on something that should be very simple...

I have a form with a calendar control.  As a new date is selected in the calendar control, I am storing the value in a temporary table so that I can allow the user to select multiple values.  The TEMP table field is a Date data type.

Before a new value is inserted, I'm first performing a check to be sure that the value wasn't already selected.  BUT, I continue to get a Type Mismatch error.

I've tried MANY different ways, but none have worked and I am completely stumped.

Temp Table:
   Tablename: tbl_TEMP_TrainingDates
   Column: TrainingDate (Date/Time datatype)

Form Calendar Control Name: TrainingCal

See VBA code below, the error occurs in the recordset string.
See notes at the bottom of the VBA code for other variations I have tried.

Dim strValDates As Recordset
 

Set strValDates = CurrentDb.OpenRecordset("SELECT TrainingDate FROM  

                              tbl_TEMP_TrainingDates WHERE TrainingDate = '" & _

                              Me.TrainingCal & "'")
 

'****************************

''ve tried the above code a dozen different ways

'--Surrounding the Me.TrainingCal with single quotes, "#" signs, both single quotes And "#" signs

'--Creating a Date variable and then assigned the TrainingCal value to this variable and then using that variable within the recordset.

'--Created a String variable and set it to the TrainingCal value

'--Tried using the CDate function to force the value to be a date

Open in new window

0
Comment
Question by:bkapla1
12 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24037734
Hi bkapla1,

WHERE TrainingDate = #" & Format(TrainingCal , "mm/dd/yy") & "# )


Good Luck!

Gary
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037739
A date comparison in SQL has to be wrapped with #'s not'

Set strValDates = CurrentDb.OpenRecordset("SELECT TrainingDate FROM  
                              tbl_TEMP_TrainingDates WHERE TrainingDate = #" & _
                              Me.TrainingCal & "#")

Of Me.TrainingCal id not in the format of MM/DD/YYYY then you will need to format it.

Set strValDates = CurrentDb.OpenRecordset("SELECT TrainingDate FROM  
                              tbl_TEMP_TrainingDates WHERE TrainingDate = #" & _
                              Format(Me.TrainingCal,"MM/DD/YYYY") & "#")

Cheers, Andrew
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24037740
If a date field ,try wrapping in # instead of single quotes
0
 

Author Comment

by:bkapla1
ID: 24037771
Thanks to all, I've tried all of the above solutions already with no luck.

However, I just decided to force within the TEMP table itself that the field will not allow duplicates.  In my Insert statement in the VBA code, I am hiding the warnings, so this will eliminate the duplicates, but is not as ideal of a solution because if the user clicks on that date on the calendar control, essentially nothing happens, so they don't get any prompts that they attempted to enter a duplicate, but then again, it does provide a feasible solution.

I am just going to go with that because as you all have pointed out above, the code that I already have tried should have worked already, but for some reason continues to error.

Thanks anyhow!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037782
Is the issue with the SQL or getting the value from the control, you can try Me.TrainingCal.Value or Me.TrainingCal.text and confirm that this is actually a date.
Chers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037800
Hi you have just started the delete request as I was entering my previous post, can you please check this and confirm if the proble you are having is with the SQL or returning the value from the control.
Cheers, Andrew
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:bkapla1
ID: 24037803
Andrew,

It's with the SQL since it is stating type mismatch.

I've also tried using the "value" at the end of the Me.TrainingCal as well, but that doesn't work.

What is odd is that I took the exact SQL statement used in the recordset and had it inserted into a text box.  Then, I copied that value and made it into a query, ran the query, and it worked fine... perhaps it's a corruption issue??  Strange....
This version above worked when I surrounded the variable with # signs, but no quotes.

So, the fact that the exact statement worked as a query, but not within VBA in a recordset statement is quite odd.
0
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
ID: 24037826
Oh dear!

Try changing your DIM

Dim strValDates As DAO.Recordset

Cheers, Andrew
0
 

Author Comment

by:bkapla1
ID: 24037839
Andrew, that was it!

AWESOME!!

Thanks for your help... I hope I can still award you points... let me try now.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037874
Isn't it strange that all the experts all went the same way (including myself) and we all missed the most obvious.
Just to confirm the problem is that RECORDSET exists in DAO as well as ADODB and if both references are selected it pick from the first one in the references list, for that reason you are advised to prefix the recordset as demonstrated.

Cheers, Andrew
0
 

Author Comment

by:bkapla1
ID: 24037881
Andrew,
 Thanks!  I never actually have used this prefix before (maybe because sometimes I forget to even DIM the variable for a recordset)...Thanks for the advice as I'll be sure to use that going forward.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037911
"maybe because sometimes I forget to even DIM the variable for a recordset" from now on I would recomend that you put OPTION EXPLICIT in your modules, this forces you to declare your variables but will save hours of pain when you mistype a variable name, the Option Explicit will error the mis typed variable providing of course you haven't mistyped it to another valid variable name.

The Option Explicit can be turned on in VBA for your new modules in the Option.

Cheers, Andrew
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now