syntax?

  • Thread starter Thread starter Miranda
  • Start date Start date
M

Miranda

hi,

could someone please help me out with the following statement

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb()
strSql = "INSERT INTO labourLog (sheetNumber, labourType, jobDesc, hours,
date, empID, empName)" & _
" VALUES (" & Me.sheet & ", '" & Me.labourType & "' , '" & Me.Text80 & "' ,
" & Me.hours & " , " & Me.currentDate & " , '" & Me.emp.Value & "', '" &
Me.emp.Column(0) & "' );"

db.Execute strSql, dbFailOnError


Set db = Nothing


me.sheet is a number
me.labourType is a string
me.text80 is a string
me.hours is a number
me.currentDate is date
me.emp.value is a string
me.emp.column(0) is a string

if i do ?strsql this is the output i get
INSERT INTO labourLog (sheetNumber, labourType, jobDesc, hours, date, empID,
empName) VALUES (123, 'minerOT' , 'Mega Bolts',3 , 30/01/2004 , 'matt1',
'Bourke, Miranda' );


but i get an error on

db.Execute strSql, dbFailOnError

any ideas?!?!

thanks a lot
 
Try:

strSql = "INSERT INTO labourLog (sheetNumber, labourType, jobDesc, hours,
[date], empID, empName)" & _
" VALUES (" & Me.sheet & ", '" & Me.labourType & "' , '" & Me.Text80 & "' ,
" & Me.hours & " , " & Format(Me.currentDate,"\#mm/dd/yyyy\#") & _
" , '" & Me.emp.Value & "', '" & Me.emp.Column(0) & "' );"

Literal date value must be in US format "mm/dd/yyyy" and must be enclosed by
hashes (#).

"date" is a bad Field name since it is a reserved word for the inbuilt
function Date(). Suggest you change the Field name to something else or if
you can't, make sure you *always* enclose date in square brackets like
[date] to avoid ambiguity that can lead to hard-to-find errors.
 
A couple of things to consider
1. date is a reserved word and when used as a column name
should be enclosed in parentheses e.g. [date]
2. date values should be enclosed in # e.g.
Me.hours & " , #" & Me.currentDate & "# , '"

Hope That Helps
Gerald Stanley MCSD
 
Miranda,

I answered this question for you in another newsgroup. Just a hint for
the future... Please don't post your question separately to more than
one group. If you really feel it is important for your message tro end
up in more than one group, cross-posting (i.e. addressing the one
message simultaneously to both groups) at least saves people from
wasting their time responding to something which someone else has
already answered perfectly well. Thanks.
 
No problem, Miranda. Hope you didn't feel "jumped on", and please come
on back when you need further help with anything.
 
Hi Miranda,

Do you receive the same error if you remove dbFailOnError?

Is your table a linked ODBC table? If so, see: BUG: DAO dbFailOnError
causes RecordsAffected to return 0
http://support.microsoft.com/default.aspx?scid=kb;en-us;195226

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| From: "Miranda" <[email protected]>
| Subject: syntax?
| Date: Fri, 30 Jan 2004 18:32:21 +1100
| Lines: 44
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.forms
| NNTP-Posting-Host: dc-129-229.bpb.bigpond.com 203.40.129.229
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:252952
| X-Tomcat-NG: microsoft.public.access.forms
|
| hi,
|
| could someone please help me out with the following statement
|
| Dim db As DAO.Database
| Dim strSql As String
|
| Set db = CurrentDb()
| strSql = "INSERT INTO labourLog (sheetNumber, labourType, jobDesc, hours,
| date, empID, empName)" & _
| " VALUES (" & Me.sheet & ", '" & Me.labourType & "' , '" & Me.Text80 & "'
,
| " & Me.hours & " , " & Me.currentDate & " , '" & Me.emp.Value & "', '" &
| Me.emp.Column(0) & "' );"
|
| db.Execute strSql, dbFailOnError
|
|
| Set db = Nothing
|
|
| me.sheet is a number
| me.labourType is a string
| me.text80 is a string
| me.hours is a number
| me.currentDate is date
| me.emp.value is a string
| me.emp.column(0) is a string
|
| if i do ?strsql this is the output i get
| INSERT INTO labourLog (sheetNumber, labourType, jobDesc, hours, date,
empID,
| empName) VALUES (123, 'minerOT' , 'Mega Bolts',3 , 30/01/2004 , 'matt1',
| 'Bourke, Miranda' );
|
|
| but i get an error on
|
| db.Execute strSql, dbFailOnError
|
| any ideas?!?!
|
| thanks a lot
|
|
|
|
 
Back
Top