Visual Basic Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free Forums > Programming > Visual Basic Programming
Reload this Page

type mismatch in VBA SELECT statement

Discuss type mismatch in VBA SELECT statement in the Visual Basic Programming forum on ASP Free.
type mismatch in VBA SELECT statement Visual Basic Programming forum discussing coding strategies, development and techniques. Go from prototype to product quickly using this robust platform.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old October 27th, 2005, 10:31 AM
evansc evansc is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Posts: 258 evansc User rank is Private First Class (20 - 50 Reputation Level)evansc User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 4 m 16 sec
Reputation Power: 5
type mismatch in VBA SELECT statement
I have the following code:

Code:
Dim stRecordSet As String
Dim strMonthYear As String
               
strMonthYear = InputBox("Please Enter Desired Month and Year in the following format: 2/2005")
If strMonthYear > "" Then


    stRecordSet = "SELECT Case_Number_VC, Case_Short_Name_VC, Received_DT, Date, Notes, Description, Case_Type_VC FROM dbo.MED_RPT_Cases_Reviewed_V" _
              & " WHERE DatePart(Date, m & " / "&yyyy) ='" & strMonthYear & "'"
              
                         
    Me.RecordSource = stRecordSet
    
Else
    DoCmd.CancelEvent
    
End If


I keep getting a type mismatch on the select statement. I did try changing the strMonthYear to a DATE type, but then I get a type mismatch on the If/Then statement.

I'm using VBA for Access 2003. Thanks for any help!
Reply With Quote
  #2  
Old October 27th, 2005, 10:38 AM
mehere's Avatar
mehere mehere is offline
Senior Sarcasm Wizardess
Click here for more information.
  
Join Date: Feb 2005
Location: Dreamland
Posts: 12,867 mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)  Folding Points: 10976 Folding Title: Novice Folder
Time spent in forums: 4 Months 4 Weeks 15 h 20 m 54 sec
Reputation Power: 1762
i believe 'date' is a reserved word ... try this:
Code:
stRecordSet = "SELECT [Case_Number_VC], [Case_Short_Name_VC], [Received_DT], [Date], [Notes], [Description], [Case_Type_VC] FROM dbo.MED_RPT_Cases_Reviewed_V WHERE DatePart(Date, m & " / "&yyyy) ='" & strMonthYear & "'"
__________________
Come JOIN the party!!!

Quote of the Month:
Retirement: Because you've given so much of yourself to the company that you don't have anything left we can use.

Questions to Ponder:
What do you do when you see an endangered animal eating an endangered plant?

iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm")
copyright© 2008 sbenj69
Reply With Quote
  #3  
Old October 27th, 2005, 10:44 AM
evansc evansc is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Posts: 258 evansc User rank is Private First Class (20 - 50 Reputation Level)evansc User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 4 m 16 sec
Reputation Power: 5
Okay - I tried that and still got the same error. And then I changed the field name in the query to avoid using "date" at all.

Code:
stRecordSet = "SELECT Case_Number_VC, Case_Short_Name_VC, Received_DT, StatusDate, Notes, Description, Case_Type_VC FROM dbo.MED_RPT_Cases_Reviewed_V" _
              & " WHERE DatePart(StatusDate, m & " / "&yyyy) ='" & strMonthYear & "'"


I still get the same error. I think the problem must be with my DatePart expression - does that look right?
Reply With Quote
  #4  
Old October 27th, 2005, 10:50 AM
mehere's Avatar
mehere mehere is offline
Senior Sarcasm Wizardess
Click here for more information.
  
Join Date: Feb 2005
Location: Dreamland
Posts: 12,867 mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)  Folding Points: 10976 Folding Title: Novice Folder
Time spent in forums: 4 Months 4 Weeks 15 h 20 m 54 sec
Reputation Power: 1762
try something like this:
Code:
vArray = split(strMonthYear, "/")

stRecordSet = "SELECT Case_Number_VC, Case_Short_Name_VC, Received_DT, StatusDate, Notes, Description, Case_Type_VC FROM dbo.MED_RPT_Cases_Reviewed_V WHERE Month(StatusDate) = '" & vArray(0) & "' AND Year(StatusDate) = '" & vArray(1) & "'"
Comments on this post
evansc agrees!
Reply With Quote
  #5  
Old October 27th, 2005, 12:41 PM
evansc evansc is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Posts: 258 evansc User rank is Private First Class (20 - 50 Reputation Level)evansc User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 1 Day 4 m 16 sec
Reputation Power: 5
Perfect - thanks!
Reply With Quote
  #6  
Old October 27th, 2005, 12:47 PM
mehere's Avatar
mehere mehere is offline
Senior Sarcasm Wizardess
Click here for more information.
  
Join Date: Feb 2005
Location: Dreamland
Posts: 12,867 mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)mehere User rank is General 12nd Grade (Above 100000 Reputation Level)  Folding Points: 10976 Folding Title: Novice Folder
Time spent in forums: 4 Months 4 Weeks 15 h 20 m 54 sec
Reputation Power: 1762
good ... glad it worked for you ...
Reply With Quote
Reply

Viewing: ASP Free Forums > Programming > Visual Basic Programming > type mismatch in VBA SELECT statement

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway. 
Stay green...Green IT