Results 1 to 4 of 4
  1. #1
    Fritzybabe is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2013
    Posts
    2

    Issue Using Access Form Values in Queries & Resultant Table Passed to Excel w/o Them

    Hi



    I am a newbie, both here and to Access. I am a competant Excel VBA programmer but in my new role I am breaking into the brave new world of databases, so please forgive me if I ask what seem like really basic questions.

    I recently set up a form in Access to run 5 queries using the same criteria for each of a 'date window'
    E.g. If run on 17th Jan the the window would be 20th Jan to 31st Jan.

    I did this automatically using a Select Case statement in the Form_Load event on today's date and assigned the correct 'date window' dates to txtStartDate.Value and txtEndDate.Value on the form, which were assigen to each query.

    All worked well and I linked the 5 tables to the output spreadsheet, via 'Get External Data' in Excel.

    In the 'Workbook_Open' event I then refreshed the data and wrote a macro to manipulate the ouput which worked perfectly. (All good so far!)

    It then went Pete Tong when I tried to get clever and create a 6th table containing the txtStartDate.Value and txtEndDate.Value in along with today's date (date run). I wanted to pass this to another Excel sheet in the workbook and use it to create the filename when the SaveAs code was run.

    The issue was that the table created was correct with 3 entries:

    1. Run_Date: Date()
    2. StartDate: Forms![10 Day Report]!txtStartDate
    3. EndDate: Forms![10 Day Report]!txtEndDate


    but the Excel sheet only had the Run_date. The Start & End dates disapeared.

    When I looked at the table in design view, the 2 fields were both 'Binary'. Changing them to text and re-running the query, changed them back to Binary and it still didn't work.

    Any ideas? (I bet it is really simple!)

    Fritzybabe
    (Windows XP, Access and Excel 2003)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'binary'? Binary is not a choice in Access table design, at least not in Access 2007. Don't know about 2003. Why not Date/Time type?

    If you want to provide files for analysis, follow instructions at bottom of my post.
    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
    Fritzybabe is offline Novice
    Windows XP Access 2003
    Join Date
    Jan 2013
    Posts
    2
    Click image for larger version. 

Name:	Table Data Type.JPG 
Views:	5 
Size:	13.6 KB 
ID:	10779

    This screen shot shows the data type issue.

    If the 2 binary data types can be changed to either Text or Date/Time and saved but as soon as I run the query, the new saved table has binary data types again.

    Here is a screenshot of the query:
    Click image for larger version. 

Name:	Query.JPG 
Views:	5 
Size:	13.0 KB 
ID:	10778
    As you can see nothing complicated.

    It is as if the Excel is 'rejecting' the 2 binary values as when the data is refreshed the screen flashes as if they are brought in but then deleted.

    If the table is copied and pasted into Excel it works fine.
    Attached Thumbnails Attached Thumbnails Table Data Type.JPG   Query.JPG  

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, Binary is not offered as a datatype in Access2007 or Access2010.

    Don't understand what running a query has to do with creating the 6th table.
    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: 4
    Last Post: 12-17-2012, 01:21 PM
  2. Creating Excel pivot table issue
    By Reaper in forum Import/Export Data
    Replies: 1
    Last Post: 06-14-2012, 12:32 PM
  3. Replies: 3
    Last Post: 02-24-2012, 01:23 PM
  4. Replies: 8
    Last Post: 02-19-2012, 03:48 PM
  5. Form Data Not being passed onto query
    By fmoses in forum Queries
    Replies: 2
    Last Post: 08-20-2009, 08:33 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