Replies

Hi!

You might be best moving this question to the Outlook forum. It sounds as if MS Access has worked correctly, but a security setting within Outlook is stopping the mesage from being sent. There’s probably a way to change the setting, but I’m no Outlook expert and others would need to advise.

There could also be a way to force use of a specific profile from the SendObject command, but if so, I’m not familiar with it.

Regards,

Baiju,

this is a simple one. You just need to change the range and the reference to the cell holding the criteria. You need: =COUNTIF($A$9:$A$16,$A2).

Since you have a value (OPS) repeated in your criteria, be careful you don’t simply total up the results!

Regards,

Andy,

Glad to be of help.

Luke,

I haven’t got a direct answer immediately, but can offer an alternative approach. What about adding the date and time to the filename, e.g.
strFilename = “myfile ” & Format(Now(), “yyyy-mm-dd”) & ” ” & Format(Now(), “hhmmss”) & “.xls”
DoCmd.OutputTo acOutputQuery, “qryMyQueryName”, acFormatXLS, strFilename, True
This makes every file name unique.

Just a thought.

Hi! Don’t know if this is any help. I prepared it a couple of years ago, and haven’t looked at it since. Still, it might give you some ideas. Look at frmHelpExplanation to start.

Andy,

could this be as simple as the database not being in the default Access folder? If so, you’ll need to add the path before the filename, e.g. “D:myfolderfilename,mdb”.

Worth a try.

Hi!

You need to add a Where clause to your SQL statement

Set RSD1 = DB1.OpenRecordset(“SELECT COUNT(*) FROM ANAGRAFICA1 WHERE ([insert your field name here] LIKE “65*”))

or you could use SELECT COUNT(*) FROM ANAGRAFICA1 WHERE (Left([insert your field name here],2) = “65”))

Try it in the query grid first, by creating your totals query with its Count function, then dragging the data field down to the grid and adding the criteria to this field as a ‘Where’. If the field is numeric, change the criteria accordingly.

Don’t forget to check for EOF or use NZ() around the recordset result, or the code will fall over.

Have fun!

Deekatz,

you haven’t actually said if Hans’ suggestion was any help. Instead, you’ve launched into a second question. Sticking to your first question, if the KB article didn’t cover your situation, more information is needed. I use Access and SQL Server 2000 together all the time, and there’s normally an explanation for such symptoms (quite often the two queries are actually slightly different, or dates are not being formatted correctly, or nulls are handled differently). If you’d like to post the SQL that you are using, I’ll see if I can come up with any ideas.

Michelle,

the only way I know to handle this is to create a new table. Copy the structure of your table, change the name of the original (for later deletion), then give the new table the correct name. Or, change the name of the original, then copy the structure to a new table with the original name. Don’t forget to check that all indexes have also transferred to the new table.

Remember that if you have a header row (as seems extremely likely), then the row with the problem may be 246 not 245. I’ve also known Excel to be misleading with these row numbers, and the problem could be a few above or below the stated row.

Jerry mentioned char(10) and char(13). The ascii character set includes characters that are not always visible on screen, such as carriage returns and, obviously, spaces. For more information, try the help file or Microsoft’s KnowledgeBase.

I’ll make this my last reply to this post, I don’t think there’s anything useful I can add. Keep trying, there’s always a solution, always something new to learn.

Hi again!

The error is possibly on row 243 of the spreadsheet, athough this is approximate. You must have a whole lot of columns in your spreadsheet to get 215 as the error column. Possibly there are cells in the spreadsheet that once held data, but it has now been deleted. Excel and Access often have a dispute over the content of these cells. I suggest you try copying your data into a nice new workbook, only copying those columns and rows that have visible data that you want to keep, and try the import again.

Sorry, no simple example. I’ve stripped out some redundant comments from the code in the attached file (and changed table names). Try following the code using my previous post as a guide. Think of it as a useful learning exercise!

JulesG

You are fighting against MS Access’ normal way of working. You may even need to look at your data structure to see if it is optimum for your system – if the data will not easily come out in the format you require, there may be something fundamentally wrong with how the data is being stored.

Putting that aside, here’s how I would approach the problem.

I like to use arrays when concatenating data; it makes the data easier to handle. For the current task, a single-dimension array is probably appropriate.

I don

I can’t think of a way to do this in a query without getting very complicated using two recordsets from the same table. You could try grouping options and a calculated field that concatenates both values, setting a criteria of circuit field = 1 to avoid duplicates, but this only works if you will never have a circuit field value of 2 without a corresponding 1.

However, If I was approaching this problem, I’d use some code to run through the records, concatenating data where appropriate, and outputting the data to a temporary table.

Just some thoughts,

Hans has provided excellent advice, but from the lack of response, I suspect that it’s not what you wanted to hear.
Since you cannot click on an invisible control, I assume that you are controlling visible/invisible from elsewhere, an option group or a series of buttons. Why not write a simple function that makes all the list boxes invisible, and then makes the required list box visible. For example, SetVisible(“controlname”):

Function SetVisible(ctlName as String)

listbox1.Visible = False
listbox2.Visible = False

ctlName.Visible = True

End Function

There are neater ways, this is simple. Hans’ solution is better.