• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Append data through RunSQL statement, need to format as text.

My problem is a field that hold period information formats the data as yyyy-mm

When I use the statement below, "2013-01" becomes "2012"  (2012 minus 1)

I've tried Cstr(stPeriod), Trim, ???

Here is what I have used

DoCmd.RunSQL "INSERT INTO tblInvTransactions ( [TicketNo], [Period], [TranDate], [InvItem], [QtyIn], [Notes])" _
& "Values (" & stTicketNo & ", " & stPeriod & ", " & "#" & stTranDate & "#" & ", " & stInvItem & ", " & stQtyIn & ", " & stNotes & ");"

Thanks for any help
0
TechGuise
Asked:
TechGuise
  • 4
1 Solution
 
Dale FyeCommented:
Try it with

", '" & stPeriod & "', "
0
 
Dale FyeCommented:
This syntax includes a single quote  before and after your yyyy-mm string, so it looks like:

'yyyy-mm'
0
 
IrogSintaCommented:
Either what fyed said or if you had assigned the value of stPeriod previously from a date, you can just use the Format function in your statement instead.

DoCmd.RunSQL "INSERT INTO tblInvTransactions ( [TicketNo], [Period], [TranDate], [InvItem], [QtyIn], [Notes])" _
& "Values (" & stTicketNo & ", " & Format([Period,  "yyyy-mm") & ", " & "#" & stTranDate & "#" & ", " & stInvItem & ", " & stQtyIn & ", " & stNotes & ");"
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Dale FyeCommented:
The other thing, I would not use the runsql command.  I use the execute method, which has a syntax:

Currentdb.execute strSQL, [options]

And if you use dbFailOnError as the option, then it will generate an error if there is a syntax error or some other error is encountered during the operation.

You can add error handling to deal with those.

The other advantage is that this method avoids the warnings that you get with action queries.
0
 
TechGuiseAuthor Commented:
That did it.   I wish a had a better grasp of when to put things in quotes, single quotes, etc...

I would have thought that would just enter the text "stPeriod" instead of the value.

Thanks......... A LOT!!!
0
 
Dale FyeCommented:
If you had done the following it would have entered 'stPeriod'

& ", 'stPeriod' ," &

The rule  is, if you want to insert a value that is stored in a a string variable (I do it for dates too) it must be wrapped.  For strings, you use single quote ( generally), for dates, you use #.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now