Solved

Data Type Mismatch using Calendar Control

Posted on 2009-04-01
12
301 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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
 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

615 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