Results 1 to 5 of 5
  1. #1
    bbxrider is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    69

    query editor putting quotes around date brackets

    trying to put a 'between date' criteria like this in the build function:
    Between #11/28/2014# And #11/29/2014#
    but after I 'ok' the build, it gets saved as:
    Between "#11/28/2014#" And "#11/29/2014#" .. the quotes get added


    then the query does not work, because
    it thinks its a string search due to the quotes?
    I even tried >=...<= instead of between and quotes got added there too!
    Access has a mind of its own?

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The field you are using that criteria with is defined as a text field. That is what Access does, puts quote in for text fields. Either the original field has been defined as a text field or you have it formatted somewhere (which has turned it into a text field).

  3. #3
    bbxrider is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Apr 2010
    Posts
    69
    thanks, that was it. I originally imported a .xls and the date field had this format, yyyy-mm-dd hh:mm:ss (note dash instead of slash), so I reformatted dash to slash and changed field type to date time in design, but lol, couldn't fool access. Had to create a new date time column and then populated it with datevalue function using the original date. however it does not convert the time, the new field is a date only. so this leaves some new additional questions, if too many or wrong place let me know and I will repost.
    is there some way during the import of the xls to a table to convert the 'text' date field with the yyyy-mm-dd hh:mm:ss format to an access date time field?
    is there a way to import the xls but as an append to an existing table with the date time conversion and preserving the time as well as the date? this would need to be a multistep process?
    do the above with vba? at this point I am a more knowledgeable with vb, and vb.net coding than using access 'tools'. Not real knowledgeable how to integrate the vb as vba in manipulating access.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The trouble with importing from Excel is that you have no control over it. With text files you can set up import specifications, but not with Excel. Access looks at the first few lines of Excel (about 8, I think it is) and makes a determination on the data type to assign. In some cases I have had to put a "dummy" line in row 1 of the Excel file in order to bring them in correctly, then delete it afterwards. Number fields which have text in them further down, comments lines that come in as text instead of memo and end up truncating, those are the sorts of problems one runs into with importing from Excel.

    So, no to your first question, no way to control the import.

    One option is to change it to a "csv" prior to importing, then you can create import specs which will determine each field's data type.

    Another option is to bring it in to a temporary file, then run an append query to your table converting the fields as you do. I tested that format in a text field and updated a Date/Time field by using - CDate(datefield) - and it converted both the date and the time correctly. Access doesn't mind about dashes or slashes, either is fine. I did my test with dashes.
    The steps for this would be
    1 - delete temp table
    2 - import to temp table
    3 - run append query
    This can be done either with a macro or with VBA if you wish to tackle that.

  5. #5
    bbxrider is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    69
    thanks very much for the thorough reply, there's a lot of good information here. Looks like will either get the vendor to supply csv instead of xls, or just convert the xls to csv. Will definitely need to keep the time component so the CDate is good to know. For an update option I will probably give the user a file list to choose from, taken from a directory where the csv's will be put. So will have to be using vba in concert with a form that will be used to manage the import. So thanks again, will probably be new posting with questions about that.

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

Similar Threads

  1. Replies: 6
    Last Post: 06-06-2014, 11:02 AM
  2. Replies: 1
    Last Post: 07-12-2013, 01:15 PM
  3. Putting double quotes around text
    By weg220 in forum Queries
    Replies: 5
    Last Post: 12-21-2012, 10:16 AM
  4. Help with putting QUERY in a Range
    By taimysho0 in forum Programming
    Replies: 3
    Last Post: 02-15-2012, 06:29 PM
  5. Search for brackets
    By stocktsi in forum Access
    Replies: 3
    Last Post: 11-14-2011, 06:18 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
  •  
Other Forums: Microsoft Office Forums