Results 1 to 14 of 14

DoCmd Run SQL

  1. #1
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177

    DoCmd Run SQL

    I am running this code and it seems to want to capture the time not the date from the form I am using. I want to insert the values into the transaction log table and it fails with the run time error 2501 and cannot append this sql to the table. It is showing the current time in the date time stamp but I need the date also.




    If (IsNull(before![SECURITY ADMINESER]) = False) And (IsNull(before![SEC SUBSYSTEM INDEX]) = False) And (IsNull(before![PROFILE INDEX]) = False) Then

    DoCmd.RunSQL "INSERT INTO [transaction log] ([user name],[secuirty system adminastrator],[sub-system],[profile],[date/time stamp],[before/after]) " _
    & "VALUES ('" & GENERAL.HOLD_UINDEX & "','" & before![SECURITY ADMINESER] & "','" & before![SEC SUBSYSTEM INDEX] & "','" & before![PROFILE INDEX] & "','" & Forms![user]![DATE/TIME STAMP] & "','" & "B" & "');"

    The error also shows that it is a type conversion failure and set 0 fields to null because of it. I also got an error saying two tables were out of sync and the obligatory contact MIS.

    The bold is where I think the issue is. Any help would be appreciated.

    thanks,
    Nick

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Use # instead ' to delimit parameter for date/time field type.

    If a field is number type, do not use any delimiters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,687

  4. #4
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177
    So I checked above the code I posted here and the Forms![user]![DATE/TIME STAMP] is = to the now () function. I read that the result of this Now () function is a delimited result not a number type. I checked the table and the field is a Date/Time Data type. Is this a correct assumption? What type of data is needed to insert into a Date/Time field?
    Thanks,
    Nick

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    date/time data goes into Date/Time field.

    Now() returns a full date and time value.

    Date() returns the date part (time part will not show and would be all 0's if it did)

    If you don't want user to select value for [date/time stamp] field then don't reference a textbox. Just reference the Now() or Date() function in the SQL.

    Regardless of which you use, don't use apostrophe delimiters when field is date/time type.

    BTW, advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177
    Quote Originally Posted by June7 View Post
    date/time data goes into Date/Time field.

    Now() returns a full date and time value.

    Date() returns the date part (time part will not show and would be all 0's if it did)

    If you don't want user to select value for [date/time stamp] field then don't reference a textbox. Just reference the Now() or Date() function in the SQL.

    Regardless of which you use, don't use apostrophe delimiters when field is date/time type.

    BTW, advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    So I changed the ' to # and there was still an error that said I made a key violation. I looked this up and it says to look up the destination table and make sure that you are not violating any Fields. My index field is set to Yes (no duplicates). How can I get around this? My table is usually blank with only the one record. So how am I creating duplicates?

    Thanks,
    Nick

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,687
    Nick,
    You may get more focused responses if you provide a copy of the database and some instructions to show the problem.
    At the bottom of June's posts there are instructions for attaching a data base to a post.

  8. #8
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177
    Quote Originally Posted by orange View Post
    Nick,
    You may get more focused responses if you provide a copy of the database and some instructions to show the problem.
    At the bottom of June's posts there are instructions for attaching a data base to a post.
    Thanks Orange,

    I think this would be tough since all of the data is on an ODBC server. Would I be able to still provide the needed data?

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,687

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    Can build/import the necessary tables into copy of frontend or another Access file, just need a few records, run compact & repair, zip files.

    The reference GENERAL.HOLD_UINDEX could also be causing an issue because of the period. Is before the name of form?

    There are misspelled words in the SQL statement. Have you really used these misspellings as the field names: secuirty, adminastrator, admineser ?

    I am not familiar with the error 'two tables are out of sync'.

    Why would a [transaction log] table have only 1 record?

    Since you reference a textbox for the date value, need # delimiters. If you used the Date() or Now() function would not.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177
    User name = 1743 Security system administrator = 12 Sub System = 28 Profile = 177 Date Time Stamp = 1/7/2019 1:26:06 PM Before/After = B

    Transaction log Other Columns: Index (Auto Number), msrepl_Tran_Version

  12. #12
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177
    Here is the error I'm getting
    Click image for larger version. 

Name:	MIS.png 
Views:	11 
Size:	12.9 KB 
ID:	36814

  13. #13
    nick243 is offline Competent Performer
    Windows 7 64bit Access 2002
    Join Date
    May 2016
    Posts
    177
    I tried to enter the data manually into the table and got this error
    Click image for larger version. 

Name:	ODBC.png 
Views:	12 
Size:	11.0 KB 
ID:	36815

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,889
    If fields are number type, do not use any delimiters. Text fields need apostrophe delimiter. Already noted what is needed for date/time type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-27-2016, 08:10 PM
  2. Replies: 1
    Last Post: 07-30-2015, 11:56 AM
  3. Replies: 6
    Last Post: 01-27-2015, 01:00 PM
  4. docmd.echo
    By jlgray0127 in forum Forms
    Replies: 1
    Last Post: 04-17-2013, 12:28 PM
  5. Replies: 10
    Last Post: 03-12-2013, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums