|
| |
SQL Date field help - Data type mismatch in criteria expression.
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
 |
SQL Date field help - Data type mismatch in criteria ex... - 6/16/2008 15:21:10
I have an "Edit" Form. My user can click their record to bring up a form with all the pre-existing data, they change the text, hit submit then new data is saved. Works well. There are 50 fields total, if they will out EVERY single record and hit submit it works great, however there are about 10 date based fields in the backend DB, if they leave ANY of the dat fields empty then I get this error:
Microsoft JET Database Engine error '80040e07'
Data type mismatch in criteria expression.
My code is almost 900 lines long so I am only going to post a sample of where I think the issue is.
<%if oktogo=1 and request.form("submit")="Update Record" then%>
<%'***** UPDATE RECORD IN DATABASE%>
<%
idtofind=replace(request.form("id"),"/","")
sql = "update tbl_Main set " _
& " Name='"&Name&"', " _
& " HomeAddress='"&HomeAddress&"', " _
& " RequestDate='"&RequestDate&"', " _
& " Sex='"&Sex&"', " _
Can I make any changes to this line to tell it is a date field so it goes on past this if it is left blank?
& " RequestDate='"&RequestDate&"', " _
Thank you all.
|
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 15:42:56
What type of database are you using?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 15:45:17
Access 2000
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 15:50:36
Give this a try. Paste this function at the top of your page:
<%
Function verifyDate(dateField)
If isDate(dateField) then
myDate = "#" & dateField & "#"
Else
myDate = "null"
End If
verifyDate = myDate
End Function
%>
Then, in your SQL, do this:
& " RequestDate="&verifyDate(RequestDate)&", " _
The function should allow the insertion of a date if a valid date is entered or insert null if no date or an invalid date.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 15:53:13
OK I will... I already have
<%function cleanme(strvalue)%>
<%cleanme=replace(strvalue,"'","''")%>
<%end function%>
at the top of my page. How should I add your function without effecting current function?
| |
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 15:57:03
I just added the functions side by side like this:
<%function cleanme(strvalue)%>
<%cleanme=replace(strvalue,"'","''")%>
<%end function%>
<%
Function verifyDate(dateField)
If isDate(dateField) then
myDate = "#" & dateField & "#"
Else
myDate = "null"
End If
verifyDate = myDate
End Function
%>
It looks like that might work. Is there anything wrong with doing it this way?
| |
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 16:08:32
It looks like everything works great the way you had me set it up. I think this is a closed case. Thank you Duane as always you come through for me! Way to go.
| |
|
|
|
BeTheBall
Posts: 6359
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 16:30:52
Great. You don't need so many delimiters on the first function. You can write it like this:
<%
function cleanme(strvalue)
cleanme=replace(strvalue,"'","''")
end function
%>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
travismp
Posts: 237
Joined: 2/1/2002
From: hutchinson ks USA
Status: offline
|
RE: SQL Date field help - Data type mismatch in criteri... - 6/16/2008 16:45:39
OK I will make that change too. I am still working on that page so it will just take a second. THANK YOU AGAIN. I tested it twice and all seems well.
Travis
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
