Really simple help needed.....

[carl6885]

I can not for the life of me see what is wrong with this, can someone else??

Dim LogError As String

LogError = "INSERT INTO tblErrors (ErrorID, Description, LoggedBy, Date, Module, SubRoutine) "
LogError = LogError & "VALUES ('" & mdlDatabaseActivity.ErrorID
LogError = LogError & "','" & mdlDatabaseActivity.ErrorDesc
LogError = LogError & "','" & mdlDatabaseActivity.currentuser
LogError = LogError & "',#" & Format(Date, "dd/mm/yyyy")
LogError = LogError & "#,'" & mdlDatabaseActivity.Module
LogError = LogError & "','" & mdlDatabaseActivity.SubRoutine & "')"

MsgBox (LogError)
CurrentDb.Execute LogError

DB table are ErrorID, Description, LoggedBy, Date, Module, Subroutine.

This type of statements earlier in my code so I really are not sure what is causing the error in the insert statement.

Please help.

Thanks

Carl

 

[pbaldy]

This should help:

http://www.baldyweb.com/ImmediateWindow.htm

What goes wrong? What's the error? What are the data types of all the fields?

 

[carl6885]

Hi paul

Thanks for getting back to me. I have narrowed it down to the Date input, the string works without.

The date field in the table is set to Short date as it my other input tables so not sure why it isn't working - I would like it to add the current date....

 

[pbaldy]

Have you tried without the Format() function? Access likes dates in US format in VBA code.

 

[mdlueck]

At first glance, comes to mind that you might be getting bit trying to embed double quote characters into the string.

Here is an example query where I get away with wrapping the string to be searched for in single quotes:

  'Define a query to look up the part from the parts table based on the partnumber of the part
  strSQL = "SELECT [p].[id],[p].[authid],[a].[username] AS [authusername],[p].[logtimestamp],[p].[prodteamid],[pt].[teamname] AS [prodteamteamname],[pt].[burdenrate] AS [prodteamburdenrate],[p].[subsystypeid],[pss].[title] AS [subsystypetitle],[p].[buyercodetypeid],[pbc].[title] AS [buyercodetypetitle],[p].[stocktypeid],[ps].[title] AS [stocktypetitle],[p].[mfgmethtypeid],[pmm].[title] AS [mfgmethtypetitle],[p].[cmdtycodetypeid],[pcc].[title] AS [cmdtycodetypetitle],[p].[quoteid],[q].[poprice] AS [quotespoprice],[q].[lttool] AS [quoteslttool],[q].[ltfa] AS [quotesltfa],[q].[ltprod] AS [quotesltprod],[q].[lttool] + [q].[ltfa] + [q].[ltprod] AS [quoteslttotal],[p].[partnumber],[p].[title],[p].[rev],[p].[ver],[p].[econumber],[p].[ecoreleasedate],[ppl].[qtyper] AS [pplqtyper],[p].[eau],[p].[stopworkflg],[p].[supplierchampion],[p].[comments]," & _
           "[jim].[partnumber] AS [jdepartnumber],[jim].[title] AS [jdetitle],[jim].[revno] AS [jderev],[jim].[imprp3] AS [jdever],[jim].[buyer] AS [jdebuyercodetypetitle],[jim].[stktyp] AS [jdestocktypetitle],[jim].[untcst] AS [jdestdcst],[jim].[sip] AS [jdesip],COALESCE(NULLIF([jim].[imprp1], ''), 'N/A') + ' / ' + COALESCE([jim].[drdl01], 'N/A') AS [jdecmdtycode] " & _
           "FROM [dbo].[products] AS [prod] " & _
           "... snipped query ... " & _
           "LEFT JOIN [dbo].[jdeitemmaster] AS [jim] on [p].[partnumber] = [jim].[partnumber] " & _
           "WHERE [p].[partnumber] = [B][COLOR=Red]'" & Me.partnumber & "'[/COLOR][/B];"

If the query really MUST have a double quote character, them embed Chr(34) into the string, as in:

strTestWithDoubleQuoteChars = "This is before" & Chr(34) & "And this is after"

 

[carl6885]

I have tried: (in case i wasn't clear and it is relevant 'Date' is referencing to the internal function)

Date
'Date'
#Date#
Format(Date,"dd/mm/yyyy")
Format(Date,"mm/dd/yyyy")

.... In my other SQL statement / sub routine that works is using Format(textbox where value is, "dd/mm/yyyy").

Im at a lose with this one...

 

[carl6885]

Michael, where is the double quote in the statement? I can not see it.

 

[pbaldy]

Michael, where is the double quote in the statement? I can not see it.

I can't either. Have you tried:

LogError = LogError & "',#" & Date()

 

[carl6885]

This is what the immediate window is giving me:

INSERT INTO tblErrors (ErrorID, Description, LoggedBy, Date, Module, SubRoutine) VALUES ('11','Division by zero','Windows 7',#27/12/2011#,'mdlDatabaseActivity','ForcedError')

 

[carl6885]

If I type in Date() it automatically removes () when I move from the line.

 

[pbaldy]

If I type in Date() it automatically removes () when I move from the line.

That's fine (it will do that sometimes); does the code work that way?

You never addressed the data types of all the fields.

 

[carl6885]

Paul, no it doesn't work that way either.

Table design:

ID - Autonumber
ErrorID - Number
Description - Memo
LoggedBy - Text
Date - Date/Time with Format of Short Date
Module - Memo
Subroutine - Memo

 

[pbaldy]

You don't want the single quotes around the ErrorID value since it's numeric.

 

[carl6885]

Oh yea newbie error. That still doesn't resolve the error however if I tell it not to insert the date then all works ok....

 

[pbaldy]

So what does the Immediate window show after fixing the quotes and with just Date without the Format function? Did you try pasting the SQL into a new query and seeing what error you get there?

 

[carl6885]

Solved it!! Paul - thanks very much for your help.

In my table the field name holding the date was labelled date. i guess as this is a reserved word it did not like it - it normally tells you off for doing so but not on this occassion.

I have remained to DateLogged and used #Date# in the SQL and it works perfectly.

Thanks again for your pointers. I will ensure I use the immediate window in the future as well as pasting into a query to test as it highlighted the word 'date' with give me the thought of reserved word.

 

[carl6885]

Solved it.

 

[pbaldy]

Glad you got it sorted out. Best to change the field name, but a workaround in code is:

VBA.Date

which will force it to use the Date function rather than the field.