Recent content by rpeare

I am trying to connect to a google sheet document. My intention is to copy the information to a spreadsheet, then import it into MS Access, but I want all the code to be done from a MS Access user interface. I found the thread listed at the bottom of this post and attempted to replicate it...

yeah if you're only dropping four tables, and it's the same four tables each time I'd just execute four drop table statements. Adding items to an array is unnecessary.

yes, I botched the second instr statement

Dim rst Dim sSQL As String sSQL = "SELECT MSysObjects.Name AS table_name, MSysObjects.Type " sSQL = sSQL & "FROM MSysObjects " sSQL = sSQL & " WHERE (((Left([Name],1))<>'~') AND ((Left([Name],4))<>'MSys') AND ((MSysObjects.Type) In (1)) AND ((MSysObjects.Flags)=0))" sSQL = sSQL & "ORDER BY...

iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname]), len([Fieldname]), null) would return the search string if exists in the field, otherwise return a null value

I think there's a fairly easy solve here. If you break your route number into the numeric portion and the alpha portion in your query, then sort on those two fields you should be golden. i.e. RN: left([Route_ID], 3) RA: right([Route_ID], len([Route_ID]) -3) This assumes the numeric portion...

mark the thread solved!

You may be right about it being an end of file character gemma, even so filesystemobject would still work you'd just use 'writeline' for everything except the last line and 'write' for the last line

Have you tried using filesystemobject commands instead? with filesystemobject you should be able to define your own end of line character Sub main() Dim f Dim fs Set fs = CreateObject("scripting.filesystemobject") Set f = fs.createtextfile(Replace(CurrentProject.Path & "\", "\\", "\") &...

Are you allowed to create your own custom functions? you could create one to lookup the value you want rather than relinking the same table multiple times. You may also be able to solve this by creating temp tables, for instance creating a pivot table where the link criteria is the primary...

if you are using a split database and distributing a front end specifically for this person I would check to see if the api call references the same driver. I've run into instances where a specific command does not exist in a newer version's driver. If you are using the same front end for...

Also, you don't use AS when you alias your table, only your field names Select TESTFIELD as OMGTEST from qryIm_Temp_SystemVersionsNMtblSystemVersions3 Q3 TESTFIELD is aliased to OMGTEST qryIm_Temp_SystemVersionsNMtblSystemVersions3 is aliased to Q3

Instead of displaying the message box immediately, you can store an 'errorsfound' variable (1 or 0) then at the end of your code running display the message box if the errorsfound value is non zero. Would also allow you do things like capture all the errors on your form and display them in a...

Dim i As Integer i = 1 ' Input 14 days Timesheet dataset Do While i <= 14 'this will do x rotations For i = 1 To 14 With Me CurrentDb.Execute "INSERT INTO tblTimesheets(Given_Name, Family_Name, Employee_ID, fortnightending," & _ "TransDate, Start, Lunch, Other, Finish, Project, Class...

if you are nesting them and you are not applying filters to the subreport then you have way more than you think you do 6*24*382*736 = 70,800 possible record combinations That still doesn't seem like much but if you try to put a criteria on the subreports for each report int he level above it...