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. - Private Sub Refresh_Click()
-
Dim rvix As String
-
Dim db As Database
-
-
-
Set db = CurrentDb
-
-
db.Execute "Delete * from historical"
-
-
rvix = "insert into historical (macro_var, date_stamp, value_var)" & _
-
"SELECT a.var_id as macro_var, a.qqy as date_stamp, Max(a.value) AS value_var" & _
-
"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" & _
-
"GROUP BY a.var_id, a.qqy;"
-
db.execute rvix
6 1733
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
Also, your spacing is incorrect, everything is running together on one line. For example: - ...
-
Max(a.value) AS value_var" & _
-
"FROM
-
...
comes out to Max(a.value) AS value_varFROM when it is evaluated before sending it to the SQL engine
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.
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. - Private Sub Refresh_Click()
-
Dim rvix As String
-
Dim db As Database
-
-
-
Set db = CurrentDb
-
-
db.Execute "Delete * from historical"
-
-
rvix = "insert into historical (macro_var, date_stamp, value_var) " & _
-
" SELECT RRSMTB_T_MACRO_DAILY.VAR_ID, Year([var_timestamp]) & " / " & DatePart('q',[var_timestamp]) AS qqy, Max(RRSMTB_T_MACRO_DAILY.VALUE) AS MaxOfVALUE " & _
-
" FROM RRSMTB_T_MACRO_DAILY " & _
-
" GROUP BY RRSMTB_T_MACRO_DAILY.VAR_ID, Year([var_timestamp]) & " / " & DatePart('q',[var_timestamp]) " & _
-
" HAVING (((RRSMTB_T_MACRO_DAILY.VAR_ID)='VIX'));"
-
-
-
db.Execute rvix
-
end sub
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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",...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |