Replies

Darn it – simple settings check would have resolved this…apologies.

Tools>Options>General> make sure error trapping is set as ‘Break on Unhandled Errors’

Thanks Paul No there is no column with a space in it..however, I have just managed to solve it.
The problem arises when the formatting of the column is indented and vertical centre.
I set the formatting to left basic cells and the column now saves as csv without the trailing space.

bit of a gotcha problem with the formatting dictating the resulting csv output.

thanks for checking Paul.

The LEFT function is a red herring!
I have a module in the database that declares a function completely unrelated to the Left/Right/Mid and I had overlooked it when including PtrSafe in the ‘Declare Function’ script.

As soon as I changed it to ‘Declare PtrSafe Function’ then the code and queries run fine once more.

thanks PATT, appreciate the quick jump in on this.
Sounds like I gotta bit of catching up to do with other macros and databases now.

The code is actually in the query field in this particular instance and is:
“Left([sort_code],2) & Mid([sort_code],4,2) & Right([sort_code],2) AS S_C,”

all pretty basic run of the mill, but the left/right/mid causes the compile error

Alan,

Using math is a very good idea and the only way I think you’ll solve this problem. Here’s my take on that approach.
SQL:

Code:

SELECT aseOrders.Order_Ref, aseOrders.YearS, aseOrders.Period, aseOrders.Session
FROM aseOrders
WHERE (((aseOrders.YearS)=2014) AND ((([Period]*1000)+[Session])>=7004 And (([Period]*1000)+[Session])<=8001));

Access Query Design:
38390-aseQDJPG
Results:
38391-aseResults
Note: The results show the calculated field for reference only as you'll note both the SQL and Query Design omit that field!

Note2: I multiplied the Period by 1000 before adding in the Session to avoid any rollover results since I didn't know how many Periods or Sessions you had.

HTH :cheers:

that certainly helps a lot thanks! Very much appreciated :cheers:

Thanks RG
partial table for illustrative purposes is:
Order_Ref YearS Period Session
W15 2014 7 1
W16 2014 7 1
W17 2014 7 1
W18 2014 7 1
W19 2014 7 4
W20 2014 7 4
W21 2014 7 4
W22 2014 7 4
W23 2014 7 4
W24 2014 7 4
W25 2014 8 1

Using the parameters of greater than/= period 7 session 1 and less than/= period 8 session 1
should return all lines of data, which it does
But if I want to see only those records where the parameters are greater than/= period 7 session 4 and less than/= period 8 session 1
I get nil returns when actually I am expecting to see Order_refs W19 to W25 incl.

Hope that helps.
I am considering using math to make the period 7 session 1 into say 71 and use that ?
thanks
Alan

Thanks, been a long busy few weeks so just back on this.
Yep, all users have same mappings at logon and having checked their sessions the mappings are remaining in place.
I have tried the files in a controlled area and have found:
File1 – links to file2 using the mapped drive definition, e.g. M:alanfile2.xlsx
File1 also links to file2 using the unc of \manccommonalanfile2.xlsx

With file2 open I changed a couple of values
Did not save file2
changed view to File1
the cells which referred to the mapped drive have updated immediately
the cells which referred to the unc location have not updated at all
I did not select to update links, just switched views to the File1

In File1, I now selected Dat>Edit Links
Both paths are indicated as containing links to external files.
I select the mapped drive link and select update (I know the data has already appeared in my file, but I selected it anyway)

I select the unc link and select update – and although the link status says OK, the data on the worksheet is not updated (I guess until I SAVE the file2)

Strange goings on, and pretty disconcerting if multiple users are using the files.

Anyone else have similar experience?
TIA
Alan

We are experiencing this problem with virtualised servers and excel 2010 files.
Link formulas cells return #REF! and on other occassions return maybe a 0 instead.
I cannot repeat the error on demand but using the replace option of replacing “=” with “=” refreshes the link and hopefully returns the correct value.
In financial terms this is priority 1 serious with incorrect sums already having been reported to powers that be.

Need a fix asap so it’s all hands on deck till it’s fixed.

Thanks Guys, the sql was very basic where 1 query was linked on one to one join with another query based on a text field.
One of those queries that you take down to a single field and it still didn’t work.
The answer – Index the linked field in the audit query.

SQL below resulted in an invalid operation

Code:

SELECT [Find duplicates for dbo_TimesheetsB].Timesheet_Number, 
qry_Timesheets_Audit.Row_Id
FROM 
qry_Timesheets_Audit RIGHT JOIN [Find duplicates for dbo_TimesheetsB] ON qry_Timesheets_Audit.Row_Id = [Find duplicates for dbo_TimesheetsB].Timesheet_Number;

the SQL below works without error – exactly same SQL

Code:

SELECT [Find duplicates for dbo_TimesheetsB].Timesheet_Number, 
qry_Timesheets_Audit.Row_Id
FROM 
qry_Timesheets_Audit RIGHT JOIN [Find duplicates for dbo_TimesheetsB] ON qry_Timesheets_Audit.Row_Id = [Find duplicates for dbo_TimesheetsB].Timesheet_Number;

Weirdly enough, the parameters are in an earlier query which feeds the end query, but I added the following in:

Code:

 Set qdf = db.QueryDefs(“qry_MT103_Output”)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm

then used:

Code:

Set rs = qdf.OpenRecordset(dbOpenSnapshot)

and I get a result without the Too few parameters error.

Forgot to add: form parameters are in the style of:
[Forms]![frm_Choice]![cmbEmployerRef]

where cmbEmployerRef is a drop down selection on the form ‘frmChoice’

Thanks John,
specified the parameters at the start so I thought that would not be part of this problem, so I then wrote the criteria direct into the query and removed the query parameters.
the export worked fine when using no form parameters.
Entering 1 form sourced parameter causes the code to error with “Too few parameters Expected:1”, so the error is being driven by the number of parameters in the actual query.

the query is not being coded in the vba but is a standard query.

short section of code in the vba is:

Code:

    Dim strfile As String
    Dim intOutputfile As Integer
    Dim strOutput As String
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Set db = CurrentDb
    

    strfile = “H:test4.txt” 
            intOutputfile = FreeFile 
            Open strfile For Output As #intOutputfile   
    Set rs = db.OpenRecordset(“qry_MT103_Output”, dbOpenDynaset)

the final line above is the point of the debug error

Cheers

Many thanks John, great starter to exactly what I need. Really appreciated.
Alan

thanks Rory. I’ve implemented the calc for counting each indiivdual element of y/m/d rather than relying on datedif.
I did notice however that the error crept in when the start day date was of a greater figure than the current day date…i.e. if the original date was 10/01/2011 then the result for todays date was 1 year, 0 month and 1 day
but if the original date was 12/01/2011 then the result was 0 year, 11 month and 194 days

Cheers
Alan

Thanks Guys, just trawling through the database now to check and correct.
Didn’t help that the 2008SM was installed blindly with nothing checked.
Thanks again.
alan