SQL - Form - text box - data type mismatch ?!

G

Guest

I have spent a LOT of hours trying to get this right - unsuccessfully -PLEASE
help !
I think it should be a lot simpler than it appears to be.
Form 4 combo boxes, 6 text boxes, user selects from combo boxes, types into
text boxes, including 3 x short date formatted ones. Table has fields set to
short date as well. I wish user to be able to click 'SAve' button without
filling in all of the date boxes. sql = :-
strSQL = "INSERT INTO [tblMachineDetails](" & _
"RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber," & _
"Toner,PurchaseDate,LastServiceDate,NextServiceDate)" & _
"VALUES(" & _
"'" & Me!cboRoom & "'," & _
"'" & Me!cboType & "'," & _
"'" & Me!cboMake & "'," & _
"'" & Me!cboModel & "'," & _
"'" & Me!cboSupplier & "'," & _
"'" & Me!txtSerial & "'," & _
"'" & Me!txtToner & "'," & _
"'" & Me!txtPurchaseDate & "'," & _
"'" & Me!txtLastService & "'," & _
IIf(IsNull(Me!txtNextService), Null, " '" & Me!txtNextService & "'")
& ");"

Debug.Print "strSQL = "; strSQL
Debug result - example :- ( when I did not type anything into txtNextService
- text box)
strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('5','3','3','1','3','111111111','55555555','13/12/2004','13/12/2005', '');

I have tried everything I can find to get IIf line to work - tearing hair
out now. Depending on font, some of the " ' s are difficult to get right.
Any help - VERY welcome !!!
Ian
 
D

Duane Hookom

Dates should be delimited with "#" and numbers require no delimiter. Try
change to this:

"'" & Me!txtToner & "'," & _
"#" & Me!txtPurchaseDate & "#," & _
"#" & Me!txtLastService & "#," & _
IIf(IsNull(Me!txtNextService), "Null", " #" & Me!txtNextService &
"#")
 
D

Douglas J. Steele

The sample values for the date fields indicate that the poster uses
dd/mm/yyyy as the Short Date format (13/12/2005)

Since Access will not interpret dd/mm/yyyy correctly for the first 12 days
of every month (except for January 1st), that means it should be:

"'" & Me!txtToner & "'," & _
Format(Me!txtPurchaseDate, "\#mm\/dd\/yyyy\#) & "," & _
Format(Me!txtLastService, "\#mm\/dd\/yyyy\#") & "," & _
IIf(IsNull(Me!txtNextService), "Null", _
Format(Me!txtNextService, "\#mm\/dd\/yyyy\#") & ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Duane Hookom said:
Dates should be delimited with "#" and numbers require no delimiter. Try
change to this:

"'" & Me!txtToner & "'," & _
"#" & Me!txtPurchaseDate & "#," & _
"#" & Me!txtLastService & "#," & _
IIf(IsNull(Me!txtNextService), "Null", " #" & Me!txtNextService &
"#")


--
Duane Hookom
MS Access MVP

al9315 said:
I have spent a LOT of hours trying to get this right -
unsuccessfully -PLEASE
help !
I think it should be a lot simpler than it appears to be.
Form 4 combo boxes, 6 text boxes, user selects from combo boxes, types
into
text boxes, including 3 x short date formatted ones. Table has fields set
to
short date as well. I wish user to be able to click 'SAve' button without
filling in all of the date boxes. sql = :-
strSQL = "INSERT INTO [tblMachineDetails](" & _
"RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber," & _
"Toner,PurchaseDate,LastServiceDate,NextServiceDate)" & _
"VALUES(" & _
"'" & Me!cboRoom & "'," & _
"'" & Me!cboType & "'," & _
"'" & Me!cboMake & "'," & _
"'" & Me!cboModel & "'," & _
"'" & Me!cboSupplier & "'," & _
"'" & Me!txtSerial & "'," & _
"'" & Me!txtToner & "'," & _
"'" & Me!txtPurchaseDate & "'," & _
"'" & Me!txtLastService & "'," & _
IIf(IsNull(Me!txtNextService), Null, " '" & Me!txtNextService &
"'")
& ");"

Debug.Print "strSQL = "; strSQL
Debug result - example :- ( when I did not type anything into
txtNextService
- text box)
strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('5','3','3','1','3','111111111','55555555','13/12/2004','13/12/2005',
'');

I have tried everything I can find to get IIf line to work - tearing hair
out now. Depending on font, some of the " ' s are difficult to get right.
Any help - VERY welcome !!!
Ian
Click to expand...
Click to expand...
 
D

Duane Hookom

Good catch Doug. I fairly non-USA challenged when it comes to identifying
and correcting issues with dates.
--
Duane Hookom
MS Access MVP

Douglas J. Steele said:
The sample values for the date fields indicate that the poster uses
dd/mm/yyyy as the Short Date format (13/12/2005)

Since Access will not interpret dd/mm/yyyy correctly for the first 12 days
of every month (except for January 1st), that means it should be:

"'" & Me!txtToner & "'," & _
Format(Me!txtPurchaseDate, "\#mm\/dd\/yyyy\#) & "," & _
Format(Me!txtLastService, "\#mm\/dd\/yyyy\#") & "," & _
IIf(IsNull(Me!txtNextService), "Null", _
Format(Me!txtNextService, "\#mm\/dd\/yyyy\#") & ")"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Duane Hookom said:
Dates should be delimited with "#" and numbers require no delimiter. Try
change to this:

"'" & Me!txtToner & "'," & _
"#" & Me!txtPurchaseDate & "#," & _
"#" & Me!txtLastService & "#," & _
IIf(IsNull(Me!txtNextService), "Null", " #" & Me!txtNextService &
"#")


--
Duane Hookom
MS Access MVP

al9315 said:
I have spent a LOT of hours trying to get this right -
unsuccessfully -PLEASE
help !
I think it should be a lot simpler than it appears to be.
Form 4 combo boxes, 6 text boxes, user selects from combo boxes, types
into
text boxes, including 3 x short date formatted ones. Table has fields
set to
short date as well. I wish user to be able to click 'SAve' button
without
filling in all of the date boxes. sql = :-
strSQL = "INSERT INTO [tblMachineDetails](" & _
"RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber," & _
"Toner,PurchaseDate,LastServiceDate,NextServiceDate)" & _
"VALUES(" & _
"'" & Me!cboRoom & "'," & _
"'" & Me!cboType & "'," & _
"'" & Me!cboMake & "'," & _
"'" & Me!cboModel & "'," & _
"'" & Me!cboSupplier & "'," & _
"'" & Me!txtSerial & "'," & _
"'" & Me!txtToner & "'," & _
"'" & Me!txtPurchaseDate & "'," & _
"'" & Me!txtLastService & "'," & _
IIf(IsNull(Me!txtNextService), Null, " '" & Me!txtNextService &
"'")
& ");"

Debug.Print "strSQL = "; strSQL
Debug result - example :- ( when I did not type anything into
txtNextService
- text box)
strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('5','3','3','1','3','111111111','55555555','13/12/2004','13/12/2005',
'');

I have tried everything I can find to get IIf line to work - tearing
hair
out now. Depending on font, some of the " ' s are difficult to get
right.
Any help - VERY welcome !!!
Ian
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks you both so much for the help, would never have got this one solved !!
It nearly works !!
This Works!!!
................"'" & Me!txtToner & "'," & _
Format(Me!txtPurchaseDate, "\#dd\/mm\/yyyy\#") & "," & _
Format(Me!txtLastService, "\#dd\/mm\/yyyy\#") & "," & _
Format(Me!txtNextService, "\#dd\/mm\/yyyy\#") & ")"

This does not !?
Format(Me!txtPurchaseDate, "\#dd\/mm\/yyyy\#") & "," & _
Format(Me!txtLastService, "\#dd\/mm\/yyyy\#") & "," & _
IIf(ISNull(Me!txtNextService),"Null", _
Format(Me!txtNextService,"\#dd\/mm\/yyyy\#") & ")"

This does not !? – looking for a separator
IIF(Is---- to & ")" typed as one line
Out of curiosity could you please also tell me what the / does inthe date
format - I do not understand it ?
Many thanks again for the help - it is fantastic - we are lucky to have you
around !!!
Ian
 
R

RoyVidar

al9315 wrote in message
Thanks you both so much for the help, would never have got this one
solved !! It nearly works !!
This Works!!!
................"'" & Me!txtToner & "'," & _
Format(Me!txtPurchaseDate, "\#dd\/mm\/yyyy\#") & "," & _
Format(Me!txtLastService, "\#dd\/mm\/yyyy\#") & "," & _
Format(Me!txtNextService, "\#dd\/mm\/yyyy\#") & ")"

This does not !?
Format(Me!txtPurchaseDate, "\#dd\/mm\/yyyy\#") & "," & _
Format(Me!txtLastService, "\#dd\/mm\/yyyy\#") & "," & _
IIf(ISNull(Me!txtNextService),"Null", _
Format(Me!txtNextService,"\#dd\/mm\/yyyy\#") & ")"

This does not !? – looking for a separator
IIF(Is---- to & ")" typed as one line
Out of curiosity could you please also tell me what the / does inthe
date format - I do not understand it ?
Many thanks again for the help - it is fantastic - we are lucky to
have you around !!!
Ian
Click to expand...

It might look like the first one works, but really it does not. The
reason is that you have changed the date format. You *must* use either
US format mm/dd/yyyy (month first!) or for instance the ISO 8601
date format, which looks like this yyyy-mm-dd. Else it will only work
correct for dates 13-31, because to the Jet engine 3/4/2006 means 4th
of
march.

The error you get, I would guess it is a compile error, due to not
closing the IIF function (missing closing parenthesis).

....
IIf(ISNull(Me!txtNextService),"Null", _
Format(Me!txtNextService,"\#dd\/mm\/yyyy\#")) & ")"

Some characters have special meanings. With the format function, the /
character means something like "go to the regional settings and fetch
the local date separator", so when preceeding this with \, which is an
escape character we tell Access to use the litteral / in stead.

For instance on my settings
? format(date, "mm/dd/yyyy") -> 04.15.2006
? format(date, "mm\/dd\/yyyy) -> 04/15/2006

Where the first one, will barf when used in SQL ;-)
 
D

Douglas J. Steele

As Roy states, I suspect it works with dd/mm/yyyy only because the test
dates you used had values of 13 or higher for the day. On May 1st, you're
going to find that it's treating 01/05/2006 as 5 Jan, not 1 May.

Don't know why the IIf statement isn't working, although if you really do
have everything typed on one line, the underscore character after "Null", _
shouldn't be there (that's a line continuation character). Note, though,
that the IIf statement isn't really necessary. Change your format statement
to:

Format(Me!txtNextService,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")

The \ in the Format is an escape character: it means that whatever follows
will be used as a literal character, rather than as a formatting character.
So, having \# means that the # will be included as part of the string that's
returned by the Format function. If you didn't include the \, # would be
interpretted as a digits placeholder. The reason for the \/ is to ensure
that / is used as the date separator in the string. If you simply put /, the
Format statement would actually replace it with whatever date separator
character has been defined in Regional Settings. Sometimes that's .,
sometimes that's -, and Access can have problems with either of those
characters in dates in SQL statements. The Format specified above takes
advantage of the fact that you can specify different formatting arguments
for different values. When you have four sections, as above, the first
section apples to positive values, the second to negative values, the third
to zeroes, and the fourth to Null values. The above should more correctly be
written as ,"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyyy\#;\#mm\/dd\/yyyy\#;\N\u\l\l",
but the section for zero values (the 3rd section) is only required for dates
of 30 Dec, 1899, and the negative section (the 2nd section) for dates prior
to that, so there's likely no reason to include them in your case. The
fourth section (for Null values) will return the literal Null, since the
format has been specified as \N\u\l\l.
 
G

Guest

Much appreciate all the info - spent a few more hours trying everything ?! -
no luck ?
Private Sub cmdSave_Click()
blnAddMode = True
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String
Set cnCh1 = New ADODB.Connection
cnCh1.Open strConnection

If blnAddMode = True Then
strSQL = "INSERT INTO [tblMachineDetails](" & _
"RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber," & _
"Toner,PurchaseDate,LastServiceDate,NextServiceDate)" & _
"VALUES(" & _
"'" & Me!cboRoom & "'," & _
"'" & Me!cboType & "'," & _
"'" & Me!cboMake & "'," & _
"'" & Me!cboModel & "'," & _
"'" & Me!cboSupplier & "'," & _
"'" & Me!txtSerial & "'," & _
"'" & Me!txtToner & "'," & _
Format(Me!txtPurchaseDate,
"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyy\#;\#mm\/dd\/yyy\#;\N\u\l\l") & "," & _
Format(Me!txtLastService,
"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyy\#;\#mm\/dd\/yyy\#;\N\u\l\l") & "," & _
Format(Me!txtNextService,
"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyy\#;\#mm\/dd\/yyy\#;\N\u\l\l") & ")"

Debug.Print "strSQL = "; strSQL - SEE 1) & 2)

Else
Exit Sub
End If

Set cmdCommand.ActiveConnection = cnCh1
cmdCommand.CommandText = strSQL
cmdCommand.Execute
End Sub

1) With all date fields filled :-

strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('5','5','5','1','5','TestA','TestA',#01/01/2000#,#07/15/2001#,#12/31/2002#)

2) Leaving the NextService date field empty – then clicking Save:-
strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('3','6','5','1','5','TestB','TestB',#01/01/2000#,#01/01/2001#,)

2) Results in Runtime Error -2147217900(80040e14) – Syntax error in INSERT
INTO statement

I am now stuck !

My settings for the table fields & form text boxes are all :-

Short date format – 00/00/0000;0;_

It does get frustrating !! - Thanks again for help
 
R

RoyVidar

al9315 wrote in message
Much appreciate all the info - spent a few more hours trying
everything ?! - no luck ?
Private Sub cmdSave_Click()
blnAddMode = True
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Dim strSQL As String
Set cnCh1 = New ADODB.Connection
cnCh1.Open strConnection

If blnAddMode = True Then
strSQL = "INSERT INTO [tblMachineDetails](" & _
"RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber," & _
"Toner,PurchaseDate,LastServiceDate,NextServiceDate)" & _
"VALUES(" & _
"'" & Me!cboRoom & "'," & _
"'" & Me!cboType & "'," & _
"'" & Me!cboMake & "'," & _
"'" & Me!cboModel & "'," & _
"'" & Me!cboSupplier & "'," & _
"'" & Me!txtSerial & "'," & _
"'" & Me!txtToner & "'," & _
Format(Me!txtPurchaseDate,
"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyy\#;\#mm\/dd\/yyy\#;\N\u\l\l") & "," &
_ Format(Me!txtLastService,
"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyy\#;\#mm\/dd\/yyy\#;\N\u\l\l") & "," &
_ Format(Me!txtNextService,
"\#mm\/dd\/yyyy\#;\#mm\/dd\/yyy\#;\#mm\/dd\/yyy\#;\N\u\l\l") & ")"

Debug.Print "strSQL = "; strSQL - SEE 1) & 2)

Else
Exit Sub
End If

Set cmdCommand.ActiveConnection = cnCh1
cmdCommand.CommandText = strSQL
cmdCommand.Execute
End Sub

1) With all date fields filled :-

strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('5','5','5','1','5','TestA','TestA',#01/01/2000#,#07/15/2001#,#12/31/2002#)

2) Leaving the NextService date field empty – then clicking Save:-
strSQL = INSERT INTO
[tblMachineDetails](RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber,Toner,PurchaseDate,LastServiceDate,NextServiceDate)VALUES('3','6','5','1','5','TestB','TestB',#01/01/2000#,#01/01/2001#,)

2) Results in Runtime Error -2147217900(80040e14) – Syntax error in
INSERT INTO statement

I am now stuck !

My settings for the table fields & form text boxes are all :-

Short date format – 00/00/0000;0;_

It does get frustrating !! - Thanks again for help
Click to expand...

My guess is that the text control is ZLS not Null. Try something like
this on the last controls.

if (len(me!txtPurchasedate.value & vbNullString) > 0) then
strsql = strsql & "#" & _
format$(me!txtPurchasedate.value, "yyyy-mm-dd") & "#, "
else
strsql = strsql & "Null, "
end if
 
G

Guest

I am back!! - Many hours - with one other person trying to help me along.....
The following seems !? to work - but it does seem very messy to me. Any
suggestions ?
I very much appreciate the help of you all !!!! THANK YOU
It really does seem to me that Access in respect of wht I am trying to do is
very (over)complicated ?!
Surely lots of people wish to have date fields on forms which if user does
not have to fill .......... ?

Private Sub cmdSave_Click()
blnAddMode = True
Dim strSQL As String
Dim cmdCommand As ADODB.Command
Set cmdCommand = New ADODB.Command
Set cnCh1 = New ADODB.Connection
cnCh1.Open strConnection
If (Len(Me!txtPurchaseDate.Value & vbNullString) > 0) Then
Format (Me!txtPurchaseDate.Value = "mm\/dd\/yyyy\")
Else
txtPurchaseDate.Value = "Null"
End If
If (Len(Me!txtLastService.Value & vbNullString) > 0) Then
Format (Me!txtLastService.Value = "mm\/dd\/yyyy\")
Else
txtLastService.Value = "Null"
End If
If (Len(Me!txtNextService.Value & vbNullString) > 0) Then
Format (Me!txtNextService.Value = "mm\/dd\/yyyy\")
Else
txtNextService.Value = "Null"
End If
If blnAddMode = True Then
strSQL = "INSERT INTO tblMachineDetails(" & _
"RoomID,TypeID,MakeID,ModelID,SupplierID,SerialNumber," & _
"Toner,PurchaseDate,LastServiceDate,NextServiceDate)" & _
" VALUES(" & _
"'" & Me!cboRoom & "'," & _
"'" & Me!cboType & "'," & _
"'" & Me!cboMake & "'," & _
"'" & Me!cboModel & "'," & _
"'" & Me!cboSupplier & "'," & _
"'" & Me!txtSerial & "'," & _
"'" & Me!txtToner & "'," & _
IIf(IsNull(Me!txtPurchaseDate), "Null", Format(Me!txtPurchaseDate,
"\#mm\/dd\/yyyy\#")) & "," & _
IIf(IsNull(Me!txtLastService), "Null", Format(Me!txtLastService,
"\#mm\/dd\/yyyy\#")) & "," & _
IIf(IsNull(Me!txtNextService), "Null", Format(Me!txtNextService,
"\#mm\/dd\/yyyy\#")) & ")"

Debug.Print "strSQL = "; strSQL
Else
Exit Sub
End If

Set cmdCommand.ActiveConnection = cnCh1
cmdCommand.CommandText = strSQL
cmdCommand.Execute
Debug.Print "Got Here !"
End Sub

The only problem with the above seems to be that the word ' Null ' appears
in the text box ?!
 
D

Douglas J. Steele

I don't understand what this code is supposed to be doing:

If (Len(Me!txtPurchaseDate.Value & vbNullString) > 0) Then
Format (Me!txtPurchaseDate.Value = "mm\/dd\/yyyy\")
Else
txtPurchaseDate.Value = "Null"
End If
If (Len(Me!txtLastService.Value & vbNullString) > 0) Then
Format (Me!txtLastService.Value = "mm\/dd\/yyyy\")
Else
txtLastService.Value = "Null"
End If
If (Len(Me!txtNextService.Value & vbNullString) > 0) Then
Format (Me!txtNextService.Value = "mm\/dd\/yyyy\")
Else
txtNextService.Value = "Null"
End If

Specifically, the 3 Format statements aren't doing anything (and the Else
part is what's causing Null to appear in the text boxes)

Just remove that section of code, and see whether it works.

If not, then change the section in your Insert statement from

IIf(IsNull(Me!txtPurchaseDate), "Null", Format(Me!txtPurchaseDate,
"\#mm\/dd\/yyyy\#")) & "," & _
IIf(IsNull(Me!txtLastService), "Null", Format(Me!txtLastService,
"\#mm\/dd\/yyyy\#")) & "," & _
IIf(IsNull(Me!txtNextService), "Null", Format(Me!txtNextService,
"\#mm\/dd\/yyyy\#")) & ")"

to

IIf(Len(Me!txtPurchaseDate & vbNullString) = 0, "Null",
Format(Me!txtPurchaseDate,
"\#mm\/dd\/yyyy\#")) & "," & _
IIf(Len(Me!txtLastService & vbNullString)=0, "Null",
Format(Me!txtLastService,
"\#mm\/dd\/yyyy\#")) & "," & _
IIf(Len(Me!txtNextService & vbNullString)=0, "Null",
Format(Me!txtNextService,
"\#mm\/dd\/yyyy\#")) & ")"

or, perhaps simpler:

Format(Me!txtPurchaseDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l") & "," & _
Format(Me!txtLastService, "\#mm\/dd\/yyyy\#;;;\N\u\l\l") & "," & _
Format(Me!txtNextService, "\#mm\/dd\/yyyy\#;;;\N\u\l\l") & ")"
 
G

Guest

Doug, THANK YOU - seems like it works !!!
I am trying to create - I thought would be easy ?! - a form where the user
can enter service dates as necessary, sometimes these will be left blank. On
the cmdSave_Click Event it would store the data in a table, but I have a huge
problem with Null, "" etc. I do not fully understand all the coding, but it
seems your ' IIF(Len(Me!PurchaseDate & vbNullstring etc. etc. works. I had
got to the point where I just could not think about it any more.
Surely a lot of users wish to do what I am doing? - but leaving empty text
boxes - especially when the date is involved seems to cause huge problems ?!
I am SO HAPPY this one seems to be solved - CHEERS !!!!! (On to the next
headache !!!!)
Ian
 
Top