INSERT SQL STATEMENT 1

[ParyGoal]

Can anyone see a problem with this insert statement please?
strSQL = "INSERT INTO tblPersonalProgressReport(MngWorkWith,Date, Manager "
'strSQL = strSQL & "MngWorkWith, Date, Manager, "
strSQL = strSQL & "Plant, Week, Department, "
strSQL = strSQL & "WhatDidyouDo, Safety, Quality, "
strSQL = strSQL & "Process, ProblemsSolutions, Improvements, "
strSQL = strSQL & "NextWeek, ManagerComments, MngTrainee, DateStamp) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & Me.cboSponsor & "', "
strSQL = strSQL & "'" & Me.txtDate & "', "
strSQL = strSQL & Me.cboManager & ", "
strSQL = strSQL & Me.cboPlant & ", "
strSQL = strSQL & Me.txtWeek & ", "
strSQL = strSQL & "'" & Me.txtDepartment & "', "
strSQL = strSQL & "'" & Me.txtTask & "', "
strSQL = strSQL & "'" & Me.txtSafety & "', "
strSQL = strSQL & "'" & Me.txtQuality & "', "
strSQL = strSQL & "'" & Me.txtProcess & "', "
strSQL = strSQL & "'" & Me.txtProblemsSolutions & "', "
strSQL = strSQL & "'" & Me.txtImprovements & "', "
strSQL = strSQL & "'" & Me.txtNextWeek & "', "
strSQL = strSQL & "'" & Me.txtManagerComments & "', "
strSQL = strSQL & Me.txtMngTrainee & ", "
strSQL = strSQL & "'" & Me.txtDateStamp & "')"

I am getting an error saying that a field, control, or property could not be found by access. Error 91. As soon as it reaches the second last line of the sql statement,an error is generated

I am using ADO

Thank you

ParyGoal

 

[PHV]

What about this ?
dtDate = II(IsNull(Me!txtDate), #1900-01-01#, Me!txtDate)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ParyGoal]

Hi PHV

Thank you for the reply. That works also. What I did is ended up converting also the variable stored in the contol to CDate.

so I have
dtDate= IIF(Isnull(Me!txtDate), "1900-01-01", CDate(Me!txtDate)
or
dtDate= IIF(Isnull(Me!txtDate), #1900-01-01#, CDate(Me!txtDate). BOth statements work.

Now question out of the scope. I know it is a bad idea to use memo fields, but is there a way to allow use to enter more than 255 characters in a field without using a memo field for that field?

Thank you
ParyGoal

 

[belovedcej]

To answer your question about ADP files - actually, I have never tried it, so I don't know that it doesn't work. But I turn the file into an ADE file and then put that on each person's computer. I think I read somewhere that ADP can't be shared, but I'm not totally sure about that. But given compact and repair issues, I just find it easier to set the system to compact on close and then give each person their own copy.

As for more than 255 characters - with Access, no - a memo is the only way around that limitation. But if you are putting things in SQL Server, then yes. You can use varchar, which goes up to like 8000 or something. Just know that you shouldn't make it much bigger than you anticipate needing, because if a varchar field is 500 characters in length, but the text in that field is 4 characters, you still use 500 characters worth of memory.

 

[ParyGoal]

Thank you belovedcej for your response.
I believe that ADP files can not be shared among users. You must distribure a copy of the client application to each user.
That's one of access limitation. With VB you could share an exe file among user.

ParyGoal