1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Getting fields names in Access 2007

Discussion in 'Business Applications' started by nslemmons, Dec 15, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. nslemmons

    nslemmons Thread Starter

    Joined:
    Dec 15, 2011
    Messages:
    6
    Hello All.

    I am working on a piece of code to check what field names are in a query and then I need to pass those field names into another query.

    I found this code here on the forum and it gives me field names but I need to skip a few known field names as well.

    Any ideas?

    Sub FieldNames()
    Dim Rst As Recordset
    Dim f As Field

    Set Rst = CurrentDb.OpenRecordset("qryAccessorial_CrossTab")

    For Each f In Rst.Fields
    MsgBox (f.Name)
    Next
    Rst.Close
    End Sub
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    nslemmons, welcome to the Forum.
    Do you know the names of the fields you want to ignore?
     
  3. nslemmons

    nslemmons Thread Starter

    Joined:
    Dec 15, 2011
    Messages:
    6
    Hi and thank you.

    I do know those field names.

    They are - ord_hdrnumber, FSC, FSCM, WAITH.

    More may come up but those are ones I know about at this point.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well you can exclude them from any processing in the VBA by excluding them from the SQL statement for the new query.
     
  5. nslemmons

    nslemmons Thread Starter

    Joined:
    Dec 15, 2011
    Messages:
    6
    That's true.

    What I am after is getting these field names and then querying a table that is created from a make table query.

    The problem is the query "qryAccessorial_CrossTab" ends up with varying field names.

    I have a query with "Acc: [qryAccessorial_CrossTab]![TOTSTP]&[qryAccessorial_CrossTab]![EXP]" as a field but since the field names change it will error when one of those fields is not present.

    Maybe there's a better way to do this. I'm open to suggestions.

    Thanks,
    Nate
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Is there a reason for making a table and then querying it? Can't you just query what is currently in the tables?
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    As Rockn says a description of the whole process and it's logic might help us provide an alternative method.
     
  8. nslemmons

    nslemmons Thread Starter

    Joined:
    Dec 15, 2011
    Messages:
    6
    Hey guys.

    Sorry about the confusion on this.

    Here's what I'm working with.

    I have a table that is structured like so.

    |cht_itemcode||ivd_charge1||
    -----------------------------
    |value || value ||
    -----------------------------
    |value || value ||
    -----------------------------

    The "cht_itemcode" column has values that define the type of value that's in "ivd_charge1". I.e. "cht_itemcode" will have a value of "FSC" and "ivd_charge1" will have a corresponding value of $50.00.

    What I need to do is take certain values from "ivd_charge1" where "cht_itemcode" equals a certain value.

    What I've done in order to be able to select the fields I want is use a crosstab query to transition the "cht_itemcode" values from a row to a column. I use the crosstab to create a table. So what happens is there can be varying values in "cht_itemcode" which makes the table fields vary.

    I have a field in a query to sum all values in the table except "FSC", "FSCM", "WAITH".

    Everything else I want to sum in one column.

    I'm not sure if this helps clarify or not.

    Let me know if I can provide further explaination.

    Thanks all,
    Nate
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You could try entering the Column Headings of the Crosstab manually, which forces them to be there even if there are no values.
    If this data is going to remain fairly static, ie you don't import the data on a regular basis I would use an append query tp put the "Value" data in to a separate table linked to the itemcode.
     
  10. nslemmons

    nslemmons Thread Starter

    Joined:
    Dec 15, 2011
    Messages:
    6
    Let me back up a tad more.

    There is a query named "qryAccessorials" before the crosstab query that pulls "cht_itemcode", " ivd_charge1" and "ord_hdrnumber".

    The "cht_itemcode" is what I want to be column headers.

    So I use the "qryAccessorials" as the source for the crosstab query. The values in the "qryAccessorials" can change frequently.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Can you put the tables and queries in to an Access 2003 database for me to look at what yuo are doing?
    Don't use any personal data, just dummy data.
     
  12. nslemmons

    nslemmons Thread Starter

    Joined:
    Dec 15, 2011
    Messages:
    6
    OBP,

    I ended up manually entering the headers in the crosstab query and that did the trick. I just have to make sure that there are no added fields. Otherwise I beleive that will work.

    Thank you so much for the help.
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1031346

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice