Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2021
    Posts
    2

    Question Criteria input by date (10-06-2021) but the table/field value is numeric 44358

    Hi,



    I am struggling with the following:

    I have a Table with a date Kolom with the numeric format like 44358.
    I need to add a criteria with a question field [fill in the date] if we give an input like 10-06-2021 it does not handle it. Then I tried to make an new field to change the format to the date format I need like ďDate: CDate([numeric date field]). Fine it displayed the date I want to, but the criteria on this field does not work with a date input like 10-06-2021.
    I tried several things an search the web without result.
    I hope there is somebody with a solid solution.
    Thanks in advanceÖ.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,962
    All dates are stored as floating point double precision numbers. How they're formatted just affects the way they look. If you have not manually altered your table field alignment and it is left aligned it is text and that would be an issue. If it's right aligned, it's numeric. My regional date setting is US format (mm/dd/yyyy) but a query will work using 10-17-2021 but Access will change the input to #10/17/2021# in query design.
    the criteria on this field does not work with a date input like 10-06-2021
    That tells us nothing. It means what, error raised, no results/wrong results, something else?

    You could copy, compact and zip your db for analysis of the table and query involved as a way of getting a solution.
    What is your regional setting for date format?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    Join Date
    Jun 2021
    Posts
    2
    Quote Originally Posted by Micron View Post
    All dates are stored as floating point double precision numbers. How they're formatted just affects the way they look. If you have not manually altered your table field alignment and it is left aligned it is text and that would be an issue. If it's right aligned, it's numeric. My regional date setting is US format (mm/dd/yyyy) but a query will work using 10-17-2021 but Access will change the input to #10/17/2021# in query design.That tells us nothing. It means what, error raised, no results/wrong results, something else?

    You could copy, compact and zip your db for analysis of the table and query involved as a way of getting a solution.
    What is your regional setting for date format?
    Hi Micron,

    Thanks for your reply the tables are not imported but connected. The numeric date field from the source is a numeric field. If I give a date criteria like 11-06-2021 or 11/06-2021 it shows me nothing. Off course if I give a numeric value like 44358 it works fine. The field where I change the format to a date format gives an error that like to complex if I put the criteria on that field. Because of the connected table I am not able to change the kind/type of the field from numeric to an other field type. I attached some screenhot hope to make the situation more clear.

    Thanks in advance....Click image for larger version. 

Name:	vb1.jpg 
Views:	13 
Size:	164.0 KB 
ID:	45468Click image for larger version. 

Name:	vb2.jpg 
Views:	13 
Size:	82.3 KB 
ID:	45469Click image for larger version. 

Name:	vb3.jpg 
Views:	13 
Size:	99.0 KB 
ID:	45470

  4. #4
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    809
    From the immediate window ?
    ? cdate(44357.8261226852)
    10/06/2021 19:49:37
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,962
    I can't read anything in those pics - too small. Looks like it's not English anyway.
    The field where I change the format to a date format
    You are doing this how? The Format function returns a string (text). If you're doing that to the number field, you're changing the data type from number to text.

    Try what I already suggested - remove any formatting or conversion function and just put a valid date as criteria e.g. 6/11/21 or perhaps 6-11-2021 and see if you get any records.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,437
    Did you tried to wrap the date in # in the criteria row?

    SELECT .... WHERE CDate(Ord!Leverdatum) As Datum = #11-6-2021#

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,437
    Did you tried to wrap the date in # in the criteria row?

    SELECT .... WHERE CDate(Ord!Leverdatum) As Datum = #11-6-2021#

    Or you could try to put the criteria in the original numeric field (Leverdatum) and convert the date to double using CDbl([Enter Date])


    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,962
    (10-06-2021) but the table/field value is numeric 44358
    Umm, 44358 is 6/11/21 not 10/6/21?? No wonder it doesn't work as described.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    771
    If I look at the pictures, it's my native tongue: Dutch. So this means 99 % the date format in access is DD-MM-YY. Only, if I look, the data are linked to SQL or Oracle tables which, in general have the date format YYYY-MM-DD (that's the way in our firm which is a large global comp). I have met this problem a lot. In my SQL reports I now format the output so the users don't have a problem when downloading to Excel. Some solved it in Access by writing custom converting functions.
    But normally I would think the date number could be converted correctly using cdate.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,962
    Anyway, what does work is
    Code:
    SELECT ... CDate([calibdate]) AS DatNum FROM [tableName]
    WHERE (((CDate([calibdate]))=#6/11/2021#));
    My Access automatically converts 06-11-2021 to #6/11/2021# according to my regional settings, which I've asked about but not received an answer on that point.
    EDIT - calibdate contains a record of 44358 and the field is formatted as number (long).
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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

Similar Threads

  1. Replies: 13
    Last Post: 01-30-2019, 05:55 PM
  2. Replies: 1
    Last Post: 10-22-2014, 07:31 AM
  3. numeric field in table
    By msasan1367 in forum Access
    Replies: 13
    Last Post: 04-07-2013, 10:28 AM
  4. Just numeric input for all textboxes in the form
    By amd711 in forum Programming
    Replies: 7
    Last Post: 11-27-2012, 08:08 AM
  5. Replies: 2
    Last Post: 09-25-2011, 08:52 AM

Tags for this Thread

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 - Senior Forums