+ Reply to Thread
Results 1 to 3 of 3

VBA SQL Datevalue Problem

  1. #1
    Registered User
    Join Date
    10-02-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    2

    VBA SQL Datevalue Problem

    Hello, first post on this forum so apologies for any problems/errors.


    i am using Access as a database and Excel as a frontend of types to process data and create a report. i'm currently using a user entered date for the dates. however, i would like to use a date created field instead. my issue is the date created is a date/time instead of just a date which is throwing my code off. here is the code:

    Please Login or Register  to view this content.
    C4:I4 are just dates based on a userform input.

    This code looks to work on some sheets, not on others (resulting in a runtime of 3464). On other sheets it works perfectly and there are very few differences (only names are different).



    i have no errors if i pivot [Lead].[Date] but i do if i pivot datevalue([Lead].[Date Created]) .

    date is in format "mm/dd/yyyy". date created is in format "mm/dd/yyyy hh:mm:ss". if i try to pivot without datevalue on date created, i get no data.

    any suggestions on how to get around this? the access database is populated from an excel sheet. i have this as a direct import from VBA so i don't need to open the spreadsheet. i'd like to keep it like this for time/resource reasons. i've tried to search this on multiple forums but coming up empty handed.


    here is a snippet of the raw data from access:
    Please Login or Register  to view this content.

    Final output:
    Please Login or Register  to view this content.

    any help on this issue is greatly appreciated!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: VBA SQL Datevalue Problem

    How about wrapping part of the SQL statement that involves the "Dates" in an INT expression, so that only the date portion is used. As you are probably aware, both Excel and Access use Integers and Decimals for Dates and Times. The integer portion is the date and the decimal is the time portion of 24 hours.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    10-02-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    2

    Re: VBA SQL Datevalue Problem

    i tried INT(Lead.[Date Created]) but get a 3464 error, the same as if i used datevalue(Lead.[Date Created]).

    i forgot to mention that when i use Lead.Date, everything works perfectly. if i use Lead.[Date Created] i get nothing but blanks.

    Both Int(Lead.Date) and Lead.Date work perfectly.
    Both DateValue(Lead.[Date Created]) and INT(Lead.[Date Created]) fail with runtime 3464
    Lead.[Date Created] gives me blanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. DATEVALUE problem
    By camlad in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2010, 06:23 AM
  2. Something other than DATEVALUE
    By edwardpestian in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-09-2006, 12:05 AM
  3. DateValue Problem...
    By devrim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-24-2006, 06:15 AM
  4. DATEVALUE - Regional problem
    By EstherJ in forum Excel General
    Replies: 3
    Last Post: 05-12-2006, 06:50 AM
  5. Sumproduct Datevalue Problem
    By andyp161 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2006, 11:15 AM
  6. [SOLVED] datevalue
    By Govind in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 03:05 PM
  7. datevalue
    By LarryTheK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 10:05 AM
  8. [SOLVED] datevalue
    By LarryTheK in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1