Insert & Syntax Error

Experts,

I have a syntax and I think it is something to do with the underlined part.
do you see it?  

thanks.

     strSQL = "INSERT INTO Projects ([Status],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) " _
             & "VALUES ('7'," & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & ",'" & [TempVars]![CurrentUserID] & "','?','LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "',#" & Format(Date, "m\/d\/yyyy") & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"


Syntax
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
You didn't close out the encapsulation of the date field with #'s.  If you look at the part that reads:

 "',#" & Format(Date, "m\/d\/yyyy") & "'****APPENDED Dnb*****: "

You will notice that you are missing a closing # just before the APPEND text string.  Try adding the closing #:

 "',#" & Format(Date, "m\/d\/yyyy") & "#," & "'****APPENDED Dnb*****: "

I added the part that is in bold/underlined text.
0
Rey Obrero (Capricorn1)Commented:
try this


strSQL = "INSERT INTO Projects ([Status],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) " _
             & "VALUES ('7'," & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & ",'" & [TempVars]![CurrentUserID] & "','?','LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "','" & Format(Date, "m\/d\/yyyy") & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"
0
pdvsaProject financeAuthor Commented:
fyed:
I get a datatype mismatch


here is the debug print sql:
INSERT INTO Projects ([Status],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) VALUES ('7',1,'','?','LC Ref: (but not needed so delete it after appending if want): ' & '00440020001844x',#8/26/2013#,'****APPENDED Dnb*****: 8/26/2013')

maybe it is clear what is wrong.

thanks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pdvsaProject financeAuthor Commented:
Capricorn:  I got the same 3075 syntax error.
0
pdvsaProject financeAuthor Commented:
not sure if it makes a difference but
Projects.[Status] is Number property with row source:
SELECT tblStatus.ID, tblStatus.Status FROM tblStatus ORDER BY tblStatus.Status;
0
pdvsaProject financeAuthor Commented:
The Insert did work until I added what was underlined above in original question.
([Status],[Currency]  are the 2 additonal fields I added.

Projects.Currency is also number
0
Rey Obrero (Capricorn1)Commented:
i think you are missing a value..

specify which value should go to which field

[Status], --???
[Currency],--???
[Created By], --???
[ProjectNo], --???
[Project Name],--???
 [Start Date], --???
[Notes] --???
0
Rey Obrero (Capricorn1)Commented:
If [Status] is number use 7 instead of '7'
Currency is OK with 1
0
pdvsaProject financeAuthor Commented:
Capricorn:
<specify which value should go to which field

1.

[Status], --???            answer: 7

[Currency],--???         answer: the Dlookup

[Created By], --???     answer: TempVars

[ProjectNo], --???         answer: ?  (just a question mark)...it is text format

[Project Name],--???  answer: 'LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "',

[Start Date], --???      #" & Format(Date, "m\/d\/yyyy") & "#,

[Notes] --???           answer: " & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"
I know that must be hard to read.  I have used fyed's suggestion of closing # just before the APPEND text string.  

Actually I said the Insert worked but maybe now it didnt work.
0
pdvsaProject financeAuthor Commented:
this is what I have now:
changed '7' to just 7 and  used fyed's suggestion of closing # just before the APPEND text string.  

     strSQL = "INSERT INTO Projects ([Status2],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) " _
             & "VALUES (7," & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & ",'" & [TempVars]![CurrentUserID] & "','?','LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "',#" & Format(Date, "m\/d\/yyyy") & "#," & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"
0
pdvsaProject financeAuthor Commented:
here is the debug print :

INSERT INTO Projects ([Status2],[Currency],[Created By],[ProjectNo], [Project Name], [Start Date], Notes) VALUES (7,1,'','?','LC Ref: (but not needed so delete it after appending if want): ' & '00440020001844x',#8/26/2013#,'****APPENDED Dnb*****: 8/26/2013')
0
pdvsaProject financeAuthor Commented:
I do get datatype mismatch still
0
pdvsaProject financeAuthor Commented:
properties
 
Projects.[Status2] = Number
Projects.[Currency] = Number
Projects.[Created By] = Number
Projects.[ProjectNo] = Text
Projects.[Project Name] = Text
Projects.[Start Date] = Date / Time
Projects.[Notes]  = Memo
0
pdvsaProject financeAuthor Commented:
MAYBE THIS is easier to read:

 strSQL = "INSERT INTO Projects ([Created By],[Status2],[Currency],[ProjectNo],[Project Name],[Start Date],[Notes]) " _
             & "VALUES ('" & [TempVars]![CurrentUserID] & "'," _
             & 7 & "," _
             & DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'") & "," _
             & '?' & "," _
             & 'LC Ref: (but not needed so delete it after appending if want): ' & '" & Me.txtLCRef & "'," _
             & #" & Format(Date, "m\/d\/yyyy") & "#," _
             & "'****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy") & "')"

Open in new window



I get a compile errror line 5
0
Rey Obrero (Capricorn1)Commented:
try this


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("select * from Projects where 1=0")

With rs
    .AddNew
    ![Created By] = [TempVars]![CurrentUserID]
    ![Status2] = 7
    ![Currency] = DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'")
    ![ProjectNo] = "?"
    ![Project Name] = "LC Ref: (but not needed so delete it after appending if want): " & " & Me.txtLCRef & "
    ![Start Date] = "#" & Format(Date, "m\/d\/yyyy") & "#"
    ![Notes] = "****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy")
    .Update
End With

rs.close
0
pdvsaProject financeAuthor Commented:
dang I thought this was going to be easy.  Sorry.

I get error 3421 - Data type conversion error

let me know what is next.
0
pdvsaProject financeAuthor Commented:
Not sure if makes a difference:
Status2 is  Number with rowsource:  SELECT tblStatus.ID, tblStatus.Status FROM tblStatus ORDER BY tblStatus.Status;
0
Rey Obrero (Capricorn1)Commented:
which line?
0
pdvsaProject financeAuthor Commented:
It doesnt really say but I put a break line currency line and hovered over [Created By] and it says Null.
    ![Created By] = [TempVars]![CurrentUserID]

Maybe there needs to be a test for Null?

Null
0
Rey Obrero (Capricorn1)Commented:
where did you add the values for [TempVars]![CurrentUserID] ?
0
pdvsaProject financeAuthor Commented:
Values for [TempVars]![CurrentUserID] is set in the login screen at startup:

Private Sub cmdLogin_Click()
On Error GoTo cmdLogin_Click_Err

    If IsNull(cboCurrentEmployee) Then
     Beep
    MsgBox "You must first select an employee.", vbOKOnly, ""
   
    Exit Sub
   
    End If
   
    TempVars.Add "CurrentUserID", Me.cboCurrentEmployee.Value
0
Rey Obrero (Capricorn1)Commented:
do the Login first, before running the codes...
0
pdvsaProject financeAuthor Commented:
I tried....
I did this in the immediate window and gives Error:

?[CurrentUserID]
Error 2029
0
pdvsaProject financeAuthor Commented:
I dont know if I lose the temp variable at a certain point because it is "temp".  ON another form I have, the [currentuserID] is correct.
0
Rey Obrero (Capricorn1)Commented:
try

? [TempVars]![CurrentUserID]
0
pdvsaProject financeAuthor Commented:
well I do have code in the same form that inserts the correct value for  [TempVars]![CurrentUserID]
I am inserting on tblLetterOfCredit and not [Projects] but [Created By] has the same row source on tblLetterOfCredit as it does on [Projects].

here is a part of the code on another cmdbutton on same form:
strSQL = "INSERT INTO tblLetterOfCredit ([Created By],[LCNo] , [DateOfIssueSB], [FinalMaturity], " _
                & "[Currency], [Amount], [Comments]) " _
                & "VALUES ('" & [TempVars]![CurrentUserID] & "', '" & Me.txtLCRef & "'," _
0
pdvsaProject financeAuthor Commented:
? [TempVars]![CurrentUserID]
138

138 is correct
0
Dale FyeCommented:
pdvsa,

tempvars should remain valid as long as the application is running, so you should not be losing the value of the CurrentUserID.

ref your post:  I thought you were going to stop using that d... lookup feature.  It is confusing you and making it significantly more difficult to help you.  

Using capricorn's latest code, try:
With rs
    .AddNew
    ![Created By] = NZ([TempVars]![CurrentUserID],0)
    ![Status2] = 7
    ![Currency] = DLookup("CurrencyID", "tblCurrencyExchange", "[Currency] = '" & Me.txtCur & "'")
    ![ProjectNo] = "?"
    ![Project Name] = "LC Ref: (but not needed so delete it after appending if want): " & Me.txtLCRef
    ![Start Date] = Date()
    ![Notes] = "****APPENDED Dnb*****: " & Format(Date, "m\/d\/yyyy")
    .Update
End With

Open in new window

I changed:
1. the !Created_By line to write a zero (0) if the tempVar value is NULL
2. modified the ![Project Name] line to remove a couple of unneeded " and & symbols
3. the ![Start Date] line, because you are simply writing to the field, you don't need to use the # or Format() syntax.  Simply write the current date.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
fyed:  that was the solution.  Tempvars worked too.

I cant say what specifically the issue was.  I know of the confusion with using the Lookup feature in the table but I dont think that was the issue since I had it working before with no issue and I simply copied that dlookup that we worked on couple days ago.  

I think it was either with item 2 or 3 above [project name], [start date]

Thought this was going to be a little easier.  I dont like my questions to be like this...remember I am far from your level.  thanks for hanging in with me.  

Maybe I should split points?
0
Dale FyeCommented:
capricorn hung with you for most of this thread, while just stuck my head in at the beginning and the end.  Award points accordingly.

Whenever I want to build a SQL string for an INSERT , UPDATE, or even a SELECT statement to be used in code, I use the function below:
Public Function fnWrap(WrapWhat As Variant, Optional WrapWith As Variant = """") As String

    fnWrap = WrapWith _
           & Replace(WrapWhat, WrapWith, WrapWith & WrapWith) _
           & WrapWith
    
End Function

Open in new window

to wrap text strings in quotes and dates in the # sign.  It is a little easier to read than the method you were trying above, because you don't have all of the embedded single quotes in your text.  Given what we now know about the data types of each of your fields, from the recordset solution above, I think the following SQL string would also work:
strCriteria = "[Currency] = '" & Me.txtCur & "'"
strSQL = "INSERT INTO Projects ([Created By],[Status2],[Currency],[ProjectNo]" _
                             & "[Project Name],[Start Date],[Notes]) " _
      & "VALUES (" & [TempVars]![CurrentUserID] & "," _
                      & 7 & "," _
                      & DLookup("CurrencyID", "tblCurrencyExchange", strCriteria) & "," _
                      & "?" & "," _
                      & fnWrap("LC Ref: (but not needed so delete it after appending " _
                             & "if want): " & Me.txtLCRef) & "," _
                      & fnWrap(Date(), "#") & ", " _
                      & fnWrap("****APPENDED Dnb*****:" & Format(Date, "m\/d\/yyyy")) _
             & ")"

Open in new window

0
pdvsaProject financeAuthor Commented:
thank you for the expert help.   Im sticking with the With statement. it works.  the fnWrap and all is getting a little techy for me.  :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.