Search results

Paul, this works perfectly and has taught me something new! Thanks very much. Dan

Paul could you give me an example of how you would call the funtion within the query please? I've googled it but am struggling as i've never done this before. Thanks

Thanks all, really appreciate the response. I will try the function provided by Paul and let you know how i get on. Dan

Thanks that has fixed it although i'm still getting a #error if the field [PROMISE] is blank....i'm guessing this is a Null issue? I'll try a NZ type solution unless anyone can suggest a better way?

Hi Paul, The entrys are usually a date followed by a comment eg: 23/02/2014 - ALLOCATED (REQ DEL DATE NOT REACHED) or just the date '23/02/2014' or something like 'TBA' Thanks

Hi all, I need to clean up a date field (I hate dates in access!). The field in the table (imported from a spreadsheet) has records where users have simply entered a date in the correct format and then others where users feel obliged to add comments after the date rather in the 'Comments'...

Hi all quick update in case anyone ever has the same issue. The code above works great. Just to clarify - my original problem was that the code I used previously wouldn't work for users who didn't have Access installed locally (Clearly a big gap in my knowledge, and i'm sure there are plenty...

Hi both, I appreciate you looking at this, I also asked in my original if there was a way to write to the database using JET as an alternative. I've been able to muddle my way through this by trial and error: Dim MyConnectstr As String Dim cnConnect As ADODB.Connection Dim cmCommand As...

Right then...now getting a Runtime 429 Active X component cannot create object. Do i need to create an object when the destination Path/table is specified in the SQL or can I just run SQL?

Ok, i've adjusted my code to: Dim accApp As Object Dim strOrder As String, strCode As String 'DFII = txtDFIName.Value strSQL = "INSERT INTO [Database.mdb].PDI ([Part No],[PDI Date],[P_Score],[Risk_Score])" & _ "VALUES (""" & P & """," & "#" & dt & "#" & ",""" & S & """, " & R & ")"...

Hi all, The following code allows some users to update an access table (on a shared LAN) from an excel workbook (code is within an excel module). Dim accApp As Object Dim strOrder As String, strCode As String 'DFII = txtDFIName.Value strSQL = "INSERT INTO [PDI] ([Part No],[PDI...

Thanks for the info guys, i'll look into access runtime and split the database as per you advice, makes a lot of sense.

Hi, We have an access database stored in a shared location. One of the tables in the database is used to record transactions made in an excel spreadsheet-this is done programatically using VBA and SQL. So here is my silly question; If users don't have access installed locally will the code...

Hi, no it seems to be working fine. Pauls advice has helped loads, i was aware of Debug print but have underused it! The tip about putting SQL into a query builders SQL view has been invaluable.

Thanks Paul, Problem solved, the code in full follows. I think what also helped was removing all of the instances in my excel vba module where i had tried to force the date format...after realising that Access always wants the date passing to it in it's native mm/dd/yyyy. Dim SQL As...

Paul, I didn't have to use the debugger (Although it would have highlighted the same problem) - just had a focused look at my code.... Appreciate your time and help with this. Regards Dan

Hi Paul, Thanks for your reply but i can't get the code working, keep getting a syntax error, the full string is below and i have tried your code at stage 2 (I don't think this should cause an error?): Dim SQL As String SQL = "Update [Register1]" & _ "SET [Stage 1] = " & "#" & s1...

Hi Cronk, The code is in an excel module and is part of the update sql string: s1 is a variable linked to a dtpicker embedded in an excel userform. The date format is dd/MM/yyy. Hope this helps. Thanks

Hi hope someone can help me with this, I'm trying to pass some dates from an excel userform into access. The date is chosen using the DTPicker tool ( basically a drop down calender). I have set the property of this to custom format dd/MM/yyyy, however dates get passed to the appropriate field...

Namliam - Brilliant, thank you. Also :banghead: