Recent content by mik

  1. M

    Replacing characters in Access 2000

    2000 vs 2002 references Have checked the references on the customers Access 2000 machine, who has; Visual Basic For Applications Microsoft Access 9.0 Object Library (MSACC9.OLB) OLE Automation Microsoft Active X Data Objects 2.1 Library The only difference to my Access 2002 machine is that I...
  2. M

    Refresh Combo after load - what syntax

    Got the combo to requery Thanks Guys, the combo now refreshs. Solution was to add Me.Files_Already_Loaded.Requery (where Files_Already_Loaded is the name of the combo box) to the end of the load script.
  3. M

    Replacing characters in Access 2000

    Thanks RoyVidar, Don't need leading spaces, just easy to trim. Doing a large concatenated IIf would be ulgy and I'm gessing would give me a performance hit. InStrRev suffers from the same fate as replace on the customers machine, but in that case I had a simple alternative. Perhaps checking...
  4. M

    Refresh Combo after load - what syntax

    Hi llkhoutx, The SQL was the origional SQL that populated the combo box, it was a simple aggregrate query, something like (Have actual code at work) Select FileName from Summary_Query order by Load_Date Descending And Summary_Query is somthing like Select Sum(SMS_Quantity), FileName...
  5. M

    Replacing characters in Access 2000

    Thanks Roy, I will check the customers references on Monday. I need to fix the Microsoft DAO 3.6 one anway. Do you know what should be checked. The source file is very large, so I don't think I can put it in an array, also I know bugger all VBA (but I learning), so its a learning curve for...
  6. M

    Retrieving all data including null values (help!)

    Regardless of which dms you us, once you put a value in a where clase it causes an inner join for that value Solution is to put the condition on the join. Following example assumes that userpk is in the skills_assessment table and the condition is 11 SELECT...
  7. M

    Retrieving all data including null values (help!)

    The 'left join' syntax that Oracle uses seems to work ok in Access Select Something From First_Table Left join Second_Table on First_Table.Join_Attribute = Second_Table.Join_Attribute Lookup the IIF statement for replacing null with text. Create a new query in design view and join them...
  8. M

    Replacing characters in Access 2000

    Hi, I need to make my applicaiton compatible with Access 2000 and have found that the replace command doesn't work. Data looks like this FFFFFFFFFFF123456789 FFFFFFFFFFFFFF123456 And I need to extract the number. Currently my code replaces the 'F' with a ' ' using the 2002 Replace command...
  9. M

    Providing default values in user prompt

    Default Date is left blank Thanks llkhoutx, Good suggestion, all the user has to do is click OK Got it working with the following syntax; >=IIf([start date (dd/mm/yyyy)] Is Null,CDate('01/01/2000'),[start date (dd/mm/yyyy)]) And <=IIf([end date (dd/mm/yyyy)] Is...
  10. M

    Refresh Combo after load - what syntax

    Thanks llkhoutx, I'm nearly there, can you help with the following error message The record source 'Me!Files_Already_Loaded.RowSource = "Select......' specified on this form or report does not exist ( :confused: How do you upload pictures?)
  11. M

    count from more than one query

    Union Solution Try this SELECT Count(Q_Auth_Family_cnt.SSN) AS record_count, "FamAuthCnt" as source FROM Q_Auth_Family_cnt Group by "FamAuthCnt" Union SELECT Count(Q_Auth_MD_cnt.SSN) AS record_count, "MDAuthCnt " as source FROM Q_Auth_MD_cnt Group by "MDAuthCnt " Union SELECT...
  12. M

    Providing default values in user prompt

    Hi, I want to provide the end user a default value when prompted as in most cases the user will be using the default. SELECT Customer_Name, Customer_Start_Date FROM Customer Where Customer_Start_Date >= [Enter Start Date (dd/mm/yyyy)]; Will bring up a blank prompt for the user to enter the...
  13. M

    Refresh Combo after load - what syntax

    Hi, I have a form that uses an unbounded combo to display a summary of what is loaded. It runs a simple sql query to produce the summary. A separate button on the same form runs a vb script that loads to the table. What syntax should be added to then end of the load vb script to update the combo?
  14. M

    DoCmd.TransferText loads blank rows

    Figured this one out VBA from button; Private Sub Command0_Click() DoCmd.RunSQL "DELETE MSC_load.* FROM MSC_load" DoCmd.TransferText asImportFixed, "MSC_ddmmyy_ssss Import Specification", "MSC_Load", "C:\MSC.txt", False, "" End Sub Don't know what the [,""] does, but it fixed the problem
  15. M

    Dim db As Database returns error User-defined type not defined

    DAO must be stated Thanks Jon, have put the DAO in and now it works. An explanation for this is on Microsofts web site http://support.microsoft.com/?kbid=181542 Query returns 1 if data is present in the query and 0 if it isn't which does the trick for what I want to do. Does anyone have a...
Top Bottom