+ Reply to Thread
Results 1 to 19 of 19

Using excel VBA, import access data depending on search criteria

  1. #1
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Using excel VBA, import access data depending on search criteria

    In the attached spreadsheet I have a sheet that has in it, a table for the data to be imported into as well as 4 different search criteria (This will be about 15-20 on the actual spreadsheet but the others were removed as they are sensitive. What I would like is code that will check all 20 to see if they are being searched for. (i.e. If team says K and exact match is No then it will search for all teams with a K).

    As a second point I would like any fields that have a no in the 'search for?' column to not be included in the import column. (Headers won't be displayed in the spreadsheet until import, I have just left them there so you know what the headers are.)

    The code that I am using at the moment doesn't incoroprate the second point at all as I can't work out for the life of me how this can be done. The code in the spreadsheet will search by the top criteria on the spreeadsheet as it stands.

    Please Login or Register  to view this content.
    I would ideally like to understand how this is all done rather than just receive code but I will take anything at the moment!

    Thanks for any help.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Using excel VBA, import access data depending on search criteria

    is this the same query as
    http://www.excelforum.com/showthread...t=#post4531233
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    Yes it is.

  4. #4
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Using excel VBA, import access data depending on search criteria

    I still dont think your data is clear enough to get a handle on the problem.

    For example, your 2nd point talks about "any fields that have a no in the 'search for?' column to not be included in the import column" but your data doesnt show any of that so its not really possible to give even an idea of what should happen.

    Id recommend outputting a dozen lines from the actual table without any search criteria so people can see all the headings and the data thats in them, then show your examples. These though will need more info as well, you'll need to base them on the data youve output and show what was searched for and the expected results. Its only with that level of detail that people will be able to provide any meaningful assistance on this.

    I could guess at what you want and what the data looks like but chances are it will be way off the mark and cause you more problems than good.

  5. #5
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    In the tab 'example 1' it imports all 5 fields as they are all selected as yes

    In the tab 'example 2' it only imports the 3 fields that have yes under 'Search for?'
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    Ok, I am really struggling to explain this. Sorry.

    I have simplified my request to just having numerous criteria for searching. Here is the code that I expected to work for the search criteria.

    Please Login or Register  to view this content.
    What I realised is that the part that checks search criteria only checks the last search criteria in the code (In this case it is Nationality. Is there a way that it can check all 5 criteria?

    For example, if I put in -
    Team: G2
    Surname: Jackson
    Command: Detect
    Flight No: BA222
    Nationality: GBR

    It will only show records from the Team 'G2' with the name 'Jackson' in the command 'Detect' on Flight No: 'BA222' and the nationality 'GBR'

    I have deleted the second part as I feel that is what was causing the confusion. I hope this has cleared it up.

    Have attached another spreadsheet.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Using excel VBA, import access data depending on search criteria

    Ahhh right.... so you want it to search on all 4 criteria (Surname, Command, Flight No and Nationality) and only pull back records where all 4 match?

    at the moment what your code is doing is replacing the previous SQL with the latest one so when it first checks AJ5 and sees a Yes it sets
    Please Login or Register  to view this content.
    So at that point its set to check the Surname matches.

    You then however have another check of AJ5 which is obviously going to be true as it was checked a moment ago and nothings changed, therefore you replace the SQL above with
    Please Login or Register  to view this content.
    (in this instance though your still checking Surname to see if its LIKE var2 whereas beforehand you checked if it equalled, the 1st one isnt necessary because if its exactly the same then its definitely LIKE it.

    You keep doing these though so the last check involves you replacing the SQL again with
    Please Login or Register  to view this content.
    Which ends up being the one thats ran against the database.


    What you need is a single check of AJ5 and then set the SQL to a larger query to cover all occurrences
    ie
    Please Login or Register  to view this content.
    Im not sure however whether you also want the Team in there or is that a totally different search?

  8. #8
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    Thank you for that pjwhitfield. I would like them all as one but will do a seperate one for team too. That should be simple enough with 2 commands buttons. I have just put that code into VBa and it is coming up with a syntax error, it says that it expects end of statement at the closed bracket before the first AND.

    Any idea why this is please?

  9. #9
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Using excel VBA, import access data depending on search criteria

    you could also do something like this
    This way you do not have to split up the search in two macros and you can also search for less criteria by letting them blank and not setting exact match to yes
    For example if you leave Team and Nationality blank and setting command to exact match the query is this

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by LordLoki; 12-02-2016 at 10:18 AM.

  10. #10
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    LordLoki,

    Apologies for the delay in replying, I can only access a computer while at work. This is giving me a syntax error (missing operator) when I put it into my code.

    Thanks,

    Danny

  11. #11
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312
    Hm thats strange for me it works fine. At the moment I am on the train will upload the workbook later

    Quote Originally Posted by DannyJ View Post
    LordLoki,

    Apologies for the delay in replying, I can only access a computer while at work. This is giving me a syntax error (missing operator) when I put it into my code.

    Thanks,

    Danny

  12. #12
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    Much appreciated. .

  13. #13
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Using excel VBA, import access data depending on search criteria

    Hi, sorry for the late reply had a pretty busy day yesterday. I tested the piece of code and for me it works fine. Obviously i can not test the database part but when i run the pasted part of code in your example workbook with everything else commented out it does not give me any error.

    attached the workbook. If you still have an error make a screenshot of the debug screen so i can see where he has a problem
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    Thank you LordLoki,

    I am still getting the same error.

    Error.jpg
    Last edited by DannyJ; 12-06-2016 at 06:49 AM.

  15. #15
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Using excel VBA, import access data depending on search criteria

    If i see that correctly that is not a VBA Error this is an error from the Database it does not like the string.
    Can you paste me the Debug.print output? if you not know where it is just press ctrl+g in the vba Editor to open the immidiate Window.
    When you run the code it should print the complete SQL String there.

    greets
    Loki

  16. #16
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    SELECT * FROM SeizureInfo WHERE Team = 'G2' AND Surname = 'Jackson' AND Command = 'Detect' AND Flight No = 'BA222' AND Nationality = 'GBR'

  17. #17
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Using excel VBA, import access data depending on search criteria

    I think the problem is the field Flight No i dont have MS Access at work to test it but i think you have to write it like [Flight No] = 'BA222' in the query or change the field name in access to FlightNo generally ist not good to have space in Database Field names

  18. #18
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Using excel VBA, import access data depending on search criteria

    LordLoki, you are correct, I put [Flight No] and it worked perfectly. Such a relief to have htis resolved. Much appreciated. Rep added

  19. #19
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Using excel VBA, import access data depending on search criteria

    You are Welcome Buddy and Thanks for the Rep.

    Don't forget to set the Thread to solved if you dont have any more questions and if you need something else feel free to ask

+ 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. Import Access data into excel with VBA
    By kieranm105 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-05-2016, 11:39 AM
  2. Replies: 4
    Last Post: 11-28-2016, 10:07 AM
  3. [SOLVED] Import Specific Data From Access Into Excel
    By hack4u in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2015, 04:04 PM
  4. Import data from excel to ms access
    By mathanraj76 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-05-2013, 12:02 AM
  5. How do I import from Access to Excel - with specific criteria
    By 5habbaranks in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-19-2011, 07:41 AM
  6. Import Data from Excel into Access
    By Jollyfrog in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-23-2010, 11:16 AM
  7. Data Import Excel to Access
    By seidburns850 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2009, 05:47 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