Search results

  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...
  16. M

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

    Thanks for your help, I have managed to activate Microsoft DAO 3.6 and are getting much closer. Using the following query as a test, I have one more bug to iron out. Private Sub Test_DAO_Click() Dim dbs As Database Dim rst As Recordset Dim Sql_Str As String Set dbs = CurrentDb Sql_Str =...
  17. M

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

    Hi, I am trying to return a value from an SQL to VB and the examples tell me to either use Dim db As DAO.Database or Dim db As Database, neither of which work in my Access 2002 machine The explicit error message I get for 'Dim db As Database returns ' is 'error User-defined type not defined' I...
  18. M

    Access 2000 equiv of InStrRev and Replace

    Hi, I have built a small application in Access 2002 saving in Access 2000 format. The application runs fine on Access 2000 except that the following SQL comnmands are unknown Replace InStrRev Full syntax is; DoCmd.RunSQL "UPDATE MSC_load SET MSC_load.FileName =...
  19. M

    Browse Directory and select file the easy way

    Hi, Being new to VBA, the examples found so far on how to browse for a file and select it have been too complicated for me to use them, so I was wondering if there was an easy way to do it. This is what I have managed so far, it lets you type in the path and filename; Const BoxTitle = "Provide...
  20. M

    DoCmd.TransferText loads blank rows

    Hi, if I do a transfertext in a macro, the data loads correctly, however, when I use VBA attached to a button, the rows are loaded, but the fields are blank. What am I doing wrong? VBA from button; Private Sub Command0_Click() DoCmd.RunSQL "DELETE MSC_load.* FROM MSC_load"...
Top Bottom