Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Convert Number to Date in a query

    I am pulling data from a SQL server database. This data is then used in conjuction with an Access database.

    In one of the fields from SQL server db a date is set up as a numeric field.

    20031007

    This I want to conevt into a date field that I can then set a criteria according a date range in a query.

    My question is
    Can I use an expression in a query to convert this field to a date field.

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If your data is that consistent, i.e., YYYYMMDD, try the following, subbing your date field for the literal:

    cdate(left("20031007",4) & "/" & mid("20031007", 5,2) & "/" & mid("20031007",7,2))
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    It looks good but I cant get it to work.

    Forgive me if I am barking up the wrong tree. What I have done is,

    replaced the "20031009" and entered the [tblorders].[tbldate] in an expression in the query that holds the tblOrders table.

    This is coming back with syntax errors. Can you guide me slowly on this one.

    The brain works slowly at the beginning of the week and gets worse from then on.

    Thanks for your help

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    You'll want to have a number field to store your SQL Server data and a date field to hold your access date. These don't necessarily need to be in the same table. You can do an update query if they are and an append query if they're not.

    In the QBE grid the cdate(left([YourDateNumber],4) & "/" & mid([YourDateNumber], 5,2) & "/" & mid([YourDateNumber],7,2))

    will be in the update to row or will be in the Field row for an append.

    Whether you append or update depends on how you want to manage your data. You could put a local copy of the linked SQL Server data into your database then do an append (probably better) or you can migrate your data into your table containing both the number and the date, then do an update to the date based on the number entries. I don't know your data so I can't definitively say which is better.

    You shouldn't be getting a syntax error unless you have inadvertently taken out a comma. If the above still doesn't work, post a db with of your SQL Server Dates and the query you have used to do the massaging. Personally, I don't require any other components of the db, unless your query has user defined functions.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  5. #5
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Perfect - Just what I wanted.

    Thanks for your help

  6. #6
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Ooops - Spoke to soon.

    The query runs fine when no criteria is set against it.

    ie The date that was formatted 20031007 now appears as 07/10/2003 when I run the query.

    However, in the query, there is another table, paramdates that has a Start and End field.

    The user enters the start and end dates from a form.

    When I try to set the criteria that the records that are shown fall within these dates, I get a message "Data type mismatch in criteria expression"

    Any ideas?

  7. #7
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    A data mismatch in the criteria expression is due to your where clause, not the set or insert into. I would say there may be a problem the way the query is referencing the dates on the form, or the form data is not being entered as the query expects it.

    If the form supplies the 20031007, remember that it is supplying a number which cannot be compared to a date (well it can but you need to use the internal representation, like 350778 or something). You may need to parse the date on the form into a date formatted textbox and change your query references.

    Mike.
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •