473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL query works in query form; 'type mismatch error' when called in vba

this is my vba code assigned to a command button. the sql query works on its own. I get mismatch error when running vba. please help.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Refresh_Click()
  2. Dim rvix As String
  3. Dim db As Database
  4.  
  5.  
  6. Set db = CurrentDb
  7.  
  8. db.Execute "Delete * from historical"
  9.  
  10. rvix = "insert into historical (macro_var, date_stamp, value_var)" & _
  11.         "SELECT a.var_id as macro_var, a.qqy as date_stamp, Max(a.value) AS value_var" & _
  12.         "FROM (SELECT var_id, year('var_timestamp') & " / " & datepart('q','var_timestamp') AS qqy, value FROM rrsmtb_t_macro_daily WHERE var_id = 'VIX') AS a" & _
  13.         "GROUP BY a.var_id, a.qqy;"
  14. db.execute rvix
Jul 20 '15 #1
6 1733
jimatqsi
1,271 Expert 1GB
This can't be right
year('var_timestamp')

You're passing a string to the Year function. I suspect you need to lose the ' marks.

Jim
Jul 20 '15 #2
Rabbit
12,516 Expert Mod 8TB
Also, your spacing is incorrect, everything is running together on one line. For example:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Max(a.value) AS value_var" & _
  3.          "FROM 
  4. ...
comes out to Max(a.value) AS value_varFROM when it is evaluated before sending it to the SQL engine
Jul 20 '15 #3
jimatqsi
1,271 Expert 1GB
When I am continuing lines of code like that I always end each line with a space before the ending " and I begin the next line with a space after the first ". Just to be sure I don't run into the problem you have in this case.
Jul 20 '15 #4
this being my first activity on the forum, I was very happy and appreciative with the quick responses. jim; I adjusted the spacing of the query and even built the query in query design(which works) so that the var_timestamp passes as a date value. Still the same mismatch error remains. Here is new code with spacing and query designed.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Refresh_Click()
  2. Dim rvix As String
  3. Dim db As Database
  4.  
  5.  
  6. Set db = CurrentDb
  7.  
  8. db.Execute "Delete * from historical"
  9.  
  10. rvix = "insert into historical (macro_var, date_stamp, value_var) " & _
  11.         " SELECT RRSMTB_T_MACRO_DAILY.VAR_ID, Year([var_timestamp]) & " / " & DatePart('q',[var_timestamp]) AS qqy, Max(RRSMTB_T_MACRO_DAILY.VALUE) AS MaxOfVALUE " & _
  12.         " FROM RRSMTB_T_MACRO_DAILY " & _
  13.         " GROUP BY RRSMTB_T_MACRO_DAILY.VAR_ID, Year([var_timestamp]) & " / " & DatePart('q',[var_timestamp]) " & _
  14.         " HAVING (((RRSMTB_T_MACRO_DAILY.VAR_ID)='VIX'));"
  15.  
  16.  
  17. db.Execute rvix
  18. end sub
Jul 21 '15 #5
jforbes
1,107 Expert 1GB
When I get stuck like this, I usually try to insert the SQL into a Query Def to see what Access thinks of it.

You can put a breakpoint on line 17 and Execute the code. When the code breaks, open the Immediate Window (ctrl-G). Then type ?rvix and hit Enter into the Immediate Window. This will give you the SQL that you are attempting to execute. Next, copy this SQL onto the clipboard, and create a new SQL Definition. Then paste the SQL into the new definition and attempt to run it and see where Access thinks the error is located.
Jul 21 '15 #6
Rabbit
12,516 Expert Mod 8TB
If date_stamp is a date time value, it needs to be surrounded by pound signs. If it's a string, it needs to be surrounded by single quotes.
Jul 21 '15 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: leslie_tighe | last post by:
Hello, I have a method on a com+ object that is returning an array of objects. I know the array is popluated as calls to check the ubound and lbound show valid values. However, any calls to...
3
by: Laurel | last post by:
this is driving me crazy. i need to use a form control as a criteria in a select query, and the control's value is set depending upon what a user selects in an option group on the form. the query...
6
by: Liz Malcolm | last post by:
Hello and Thanks in advance for any help. I am using Access 2000. I have a data entry form that opens a main form (using the On Click event of the combo box ) with tab controls and 1 subform on...
19
by: sara | last post by:
I am getting "Type Mismatch Error" when the following code executes. I am trying to notify the user if she attempts to add a customer with the same FirstName, LastName, Address(line1) and City as...
5
by: David | last post by:
Hi, Getting the following error:- Microsoft JET Database Engine error '80040e07' Data type mismatch in criteria expression. The code is as follows, and the last line is the one that...
3
by: zarar | last post by:
Hi to everyon i have a problem plz help me when i filter a recordset by date in vb and ms access and enter the any date of month 04/2007 i get the error msg as type mismatch but i works for...
2
by: dstorms | last post by:
Hi, I am trying to write code that opens a form using data highlighted from a listbox, but I keep getting Type Mismatch errors. I finally ran a test code and still got the error message. Dim...
7
vikas251074
by: vikas251074 | last post by:
I am getting error above in following code since few days giving tension day and night. How can I solve this? I am facing since Oct.25. in line no. 362 After doing a lot of homework, I am...
2
by: James Blue | last post by:
Dim m_MeetingName, m_City, m_title As String Dim m_sql As Long Dim dbs As Database, rst, rst2 As Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("Related Conference Categories",...
1
by: Evelyne | last post by:
Private Sub cmdOK_Click() Dim cn As New ADODB.Connection Dim obj As New ADODB.Command Dim rst As New ADODB.Recordset On Error GoTo Err_Handler Set cn = CurrentProject.Connection
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.