Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. accessjack

    INSERT COMPOSIT PRIMARY KEY

    Thanks PHV, this worked, great tip. I have two follow up questions. 1) The tblMatrix also has 12 balance fields in it. For the part of the code above (Line 3) where you say "SELECT * FROM tblMatrix..." would I be better off specifying fields? 2) I'm not very experience with nested...
  2. accessjack

    INSERT COMPOSIT PRIMARY KEY

    PHV, Thanks for your post, the table alias trick really improves the readability of the code. You asked what happened...I got no error, just appends 0 records. Imagine this sample data already in tblMatrix: App Bank Account Year 1 1 1 2008 Now I want to add: App Bank Account...
  3. accessjack

    INSERT COMPOSIT PRIMARY KEY

    I was able to add the composite primary key by doing the following: 1) Turn warnings off 2) Remove the full WHERE clause above 3) Turn warnings back on This way access by default will try to add duplicate primary key but the error message is suppressed. Here's sample data: App Bank...
  4. accessjack

    INSERT COMPOSIT PRIMARY KEY

    Randy, I've double checked it in the debugger and the year appears to be correct. I'm pretty sure the where clause it missing parenthesis () but I'm not sure how to next them.
  5. accessjack

    INSERT COMPOSIT PRIMARY KEY

    I have two tables: tblDeposits (App, Bank, Account) and tblMatrix (App, Bank, Account, Year, Amount) The composite primary key in tblDeposits is App + Bank + Account. The composite primary key in tblMatrix is App + Bank + Account + Year. I have a form where user chooses Year from a combo box...
  6. accessjack

    Use One DB Form to Control Several DBs

    Thanks pwise. Since ultimately the form in the external db was updating a table in the external database, by linking the tables, and importing the form, I was able to update the data in the external databases locally. Thanks.
  7. accessjack

    Use One DB Form to Control Several DBs

    I would like to use one database form to run an update in 4 external access databases. Master.mdb has 1 form called frmAutomation which has one combo box called cboPeriod. I would like to add a command button to this form adding vba code to the "on click" event. When clicked, the code will...
  8. accessjack

    "INSERT INTO" vba code not working

    Worked great. Thank you.
  9. accessjack

    "INSERT INTO" vba code not working

    To append new records from tblOne to tblTwo, I use an action append query to add records where the id field in tblOne does not exist in tblTwo. I am trying to do this using vba rather than an action query. Below is my code. The code compiles and executes but 0 records are appended (no error...
  10. accessjack

    Field/Column Name Automatically Renamed

    HarleyQuinn, It worked when I did it, but when I try to run the query a little later, it did revert back again. It's weird, it's like the changes keep when I save, close & rerun the query, but when I run the subsequent query which uses the 1st query above as it's source, it just changes the...
  11. accessjack

    Field/Column Name Automatically Renamed

    Thanks, it worked when I saved it in SQL view. I never would have thought of that.
  12. accessjack

    Field/Column Name Automatically Renamed

    Yes. I tried the bracket. I got the following error message: [Adj-01] is not a valid name. Make sure that is does not include invalid characters or punctuation and that it is not too long.
  13. accessjack

    Field/Column Name Automatically Renamed

    While in query design view, I am trying to rename a column/field name to Adj-01 (see formula below; This works fine, however, when I save and exit the query, and then go back in, access renames the column/field name to Adj-1. The "0" gets ommitted. Since I am using the precise field name in a...
  14. accessjack

    Query with Dynamic # of Columns

    Thanks Ginger, it worked. I modified slightly, I had to add brackets to the fields names as it seems access doesn't like it. For i = cbostart To cboend If i < 10 Then strSQl = strSQl & ", [0" & i & "]" Else strSQl = strSQl & ", [" & i & "]" End If Next i One...
  15. accessjack

    Query with Dynamic # of Columns

    Thanks RivetHead, This is close. I have 2 issues: For month for 2 digits 10, 11, & 12, the sql code makes them 010, 011, 012. Also, for some reason I keep getting an error on the line QDef.sql = strSQl or perhaps the next line. It says error in from clause. I used a breakpoint and the...
  16. accessjack

    Query with Dynamic # of Columns

    Does anyone know how to create a select query to display a dynamic # of columns from a underlying query. I have a table called <tblMatrix> with 13 fields/columns: Account 01 02 03 04 05 06 07 08 09 10 11 12 where columns 01 thru 12 represent months with financial data or null values. I know...
  17. accessjack

    MIMIC VLOOKUP USING VBA

    This worked like a champ. It was super fast & efficent. I simply placed this code directly in the query SQL view & I replaced the hardcoded year week in the code below with the two combo boxes (Year Week) concatenated together, so now it is fully automated. Thanks. Jack
  18. accessjack

    MIMIC VLOOKUP USING VBA

    I am trying to mimic the vlookup functionality of excel in access where there isn't an exact match. tblStore has 4 fields (sample data) Store Year Week Manager A 2007 01 Jack A 2007 05 Cindy B 2007 01 Tom C 2007 26 Bill The way to read this data is Jack is the manager...
  19. accessjack

    Dyanimcally select a Table Field to Update

    Your solution worked. I tweaked things a bit. Here is my final button code on a form with 2 combo boxes: cboMonth (Jan thru Dec) & cboYear (2007 thru 2008) as drop downs. I added the year as a field to the matrix table so this will work for multiple years as well. Works perfect. Thanks...
  20. accessjack

    Dyanimcally select a Table Field to Update

    Thanks, Duane, I will give this a try today and let you know how it worked. With respect to setting up the table like a crosstab, versus setting a table up with 3 fields (account, month, amount)...I thought it would save space. I have approximately 100,000 accounts, as time went on, my table...

Part and Inventory Search

Back
Top