UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Type Mismach When Open Recordset Based On SQL String, Access 2010    
 
   
asimze
post Dec 7 2019, 03:29 PM
Post#1



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


In app use string to make SQL query, then open recordset based on on string
strSQL = "SELECT tblUposlenik.UposlenikId"
strSQL = strSQL & " FROM tblUposlenik"
strSQL = strSQL & " WHERE (((tblUposlenik.UposlenikId) Not In (SELECT tblOdsustva.UposlenikID FROM tblOdsustva WHERE (((tblOdsustva.PocetakOdsustva)<= " & Format$(Me.txtTekuciDatum, JetDateFmt) & ") AND ((tblOdsustva.KrajOdsustva)>= " & Format$(Me.txtTekuciDatum, JetDateFmt) & ")))) AND ((tblUposlenik.Aktivan)=Yes));"

Set rstUposlenici = db.OpenRecordset(strSQL)
and here get type mismach. Use final strSQL string and open query and have not problem. Use this technics milion times but here is problem???
Best regards!
Asim
Go to the top of the page
 
June7
post Dec 7 2019, 03:55 PM
Post#2



Posts: 1,394
Joined: 25-January 16
From: The Great Land


Format() function returns a string value, not a date/time nor number.

Text fields need apostrophe delimiters for parameters, date/time require # character. Example:

" WHERE [myDateField]>#" & [myDateInput] & "# AND [myTextField]='" & [myTextInput & "'"

This post has been edited by June7: Dec 7 2019, 03:55 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
asimze
post Dec 7 2019, 04:03 PM
Post#3



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


This Format$(Me.txtTekuciDatum, JetDateFmt) return correct string! JetDateFmt do this, #!!!
When I use string strSQL, and manually make new query it is OK, no problem.
Go to the top of the page
 
GroverParkGeorge
post Dec 7 2019, 05:37 PM
Post#4


UA Admin
Posts: 37,239
Joined: 20-June 02
From: Newcastle, WA


Format$() returns a string. It does not return a date.

The value it returns looks like it should be date because of the layout mm/dd/yyyy; that's true. However, it is a string.

I don't recognize the "JetDateFmt" constant you are using. Perhaps you can provide a reference to explain what that is supposed to mean. I suspect it is actually something that is defined within your VBA.
This post has been edited by GroverParkGeorge: Dec 7 2019, 05:43 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
June7
post Dec 7 2019, 05:37 PM
Post#5



Posts: 1,394
Joined: 25-January 16
From: The Great Land


I have never used $ functions. Not familiar with JetDateFmt constant. Regardless, Format function with or without $ results in a string.

Date/Time field stores value as a double number. Comparing number to string causes data type mismatch error in WHERE clause.

This post has been edited by June7: Dec 7 2019, 05:40 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
asimze
post Dec 7 2019, 05:47 PM
Post#6



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


Global Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l". I use it very often without any problem.
I use it milion time, and always correctly do it. While not now?
To remind You, when use strSQL from Breakpoint to make query ( for testing purpose) it give correctly result, query with data!!!
Go to the top of the page
 
asimze
post Dec 7 2019, 05:54 PM
Post#7



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


Live example strSQL
SELECT tblUposlenik.UposlenikId FROM tblUposlenik WHERE (((tblUposlenik.UposlenikId) Not In (SELECT tblOdsustva.UposlenikID FROM tblOdsustva WHERE (((tblOdsustva.PocetakOdsustva)<= #11/14/2019#) AND ((tblOdsustva.KrajOdsustva)>= #11/14/2019#)))) AND ((tblUposlenik.Aktivan)=Yes));
It make Type mismach.
Go to the top of the page
 
June7
post Dec 7 2019, 07:11 PM
Post#8



Posts: 1,394
Joined: 25-January 16
From: The Great Land


Try removing each criteria one at a time and see which one is really cause. Use True or -1 instead of Yes (although Yes should work for Yes/No field).

This post has been edited by June7: Dec 7 2019, 07:11 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
theDBguy
post Dec 7 2019, 09:54 PM
Post#9


UA Moderator
Posts: 78,094
Joined: 19-June 07
From: SunnySandyEggo


Hi Asim. Pardon me for jumping in, but how did you declare the variable "rstUposlenici"? Try declaring it like this:

Dim rstUposlenici As DAO.Recordset


--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
asimze
post Dec 8 2019, 03:14 AM
Post#10



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


Only forget in Reference:
Microsoft Visual Basic for Applications Extensibility 5.3.
All other is OK.
Guys thanks for your efforts!

Go to the top of the page
 
AlbertKallal
post Dec 8 2019, 05:52 AM
Post#11


UtterAccess VIP
Posts: 3,056
Joined: 12-April 07
From: Edmonton, Alberta Canada


A few things:

JetDateFmt

That threw the whole party here for a loop de doo, since we did not realize that you define this constant. Ok, that’s looks good and it been cleared up.

However, keep in mind that you STILL must place # around the date. Your code is missing this.

And for testing?

RIGHT before your set rstUposlenici = db.OpenReordSet(strSQL), put a

Debug.print strSQL

Now, after you run above and it fails, hit ctrl-g.

From the debug window you see the actual SQL. You can even cut + paste that SQL into the query builder and try it. You see that your example is MISSING the # that are required to surround the date value.

So, you need to add the # to your string as others have pointed out.

Try code like this:
CODE
   Dim strSQL           As String
   Dim db               As DAO.Database
   Dim rstUposlenici    As DAO.Recordset
   Dim dtCheck          As String

  
   dtCheck = "#" & Format$(Me.TxtTekciDatum, JetDateFmt) & "#"

   strSQL = "SELECT tblUposlenik.UposlenikId FROM tblUposlenik " & _
            "WHERE tblUposlenik.UposlenikId Not In " & _
            "(SELECT tblOdsustva.UposlenikID FROM tblOdsustva " & _
            "WHERE (tblOdsustva.PocetakOdsustva <= " & dtCheck & _
            " AND tblOdsustva.KrajOdsustva >= " & dtCheck & ") AND " & _
            "(tblUposlenik.Aktivan = Yes)  );"
  
   Debug.Print strSQL
   Set rstUposlenici = CurrentDb.OpenRecordset(strSQL)



Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
 
June7
post Dec 8 2019, 06:37 AM
Post#12



Posts: 1,394
Joined: 25-January 16
From: The Great Land


I tested that JetDateFmt format parameter and it does create SQL string with the # delimiters and it works. Cannot explain why it fails for you.

Albert, if you include additional # characters, they get doubled and the query fails.

This post has been edited by June7: Dec 8 2019, 06:40 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
cheekybuddha
post Dec 8 2019, 08:06 AM
Post#13


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


@June, I suspect that the date format is a red herring here.

More likely the recordset was not declared properly (as DBG pointed out)

--------------------


Regards,

David Marten
Go to the top of the page
 
asimze
post Dec 8 2019, 10:23 AM
Post#14



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


Albert, global constant JetDateFmt is excellent, correctly add # # on easiest way, and I use it always.
Sorry for don't add all declaration parts, because I use transaction.
June7, only this reference was problem. To be honest I don't understand while we need to use this reference!!!
Just: Microsoft Visual Basic for Applications Extensibility 5.3.
Once again, guys thanks!
Best regards.
Asim.
Go to the top of the page
 
AlbertKallal
post Dec 8 2019, 03:06 PM
Post#15


UtterAccess VIP
Posts: 3,056
Joined: 12-April 07
From: Edmonton, Alberta Canada


QUOTE
Albert, if you include additional # characters, they get doubled and the query fails.


Ok, then your format is ok, and you don't have to add the # delimiters as I have.

So, assuming you fixed that, then from the debug.window, simply post the resulting SQL string here, and we should be able to see the error.

Before you post that debug.print SQL result here, ALSO TAKE the SQL, and create a blank new query, change view to SQL mode, and then paste in your SQL.

does it give an error when you try to run it?

R
Albert
Go to the top of the page
 
asimze
post Dec 8 2019, 04:03 PM
Post#16



Posts: 176
Joined: 12-December 12
From: Bosna i Hercegovina


Albert,
strSQL string was always correct when I test it manually on new query. No problem. I newer change strSQL string!!!
Problem start when code try to make recordset based on this SQL string.
Set rstUposlenici = db.OpenRecordset(strSQL)
After adding reference: Microsoft Visual Basic for Applications Extensibility 5.3, code can make recordset which is correct.
Best regards!
Asim
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 04:46 PM