Search results

Hi everyone, I'm working on an Access datbase with a Microsoft SQL Server back-end. Unfortunately we have to use a service account to connect to the back-end, so to add a bit of security I'm trying to clear each query's connection string after every time it has been used (users will have a...

This solution seems to work just as you said. Thanks!

Hi everyone, I have some problems with NewQueryDef adding undesired (illegal) characters to my PassThrough Query that the iSeries Access ODBC Driver can't handle. For instance, I have an SQL string like this: sql_str = "SELECT GLCO FROM JD_CSEFP7.F0911" Set qdf = db.CreateQueryDef("Q01_PT"...

I somehow managed to get it to work in the end. I replaced all null values for the comment field with "" and set "" as its default value, and then used a where clause something like: WHERE Comment Not Like "*abc*". Thanks for all help.

Thank you, but it seems the problem has nothing to do with the condition itself. Even this doesn't work: SELECT * FROM tbl_a WHERE Comment <> ""; It will return 0 rows, even though there should be thousands. Perhaps my table is simply corrupt somehow.

Hi everyone, I have a table with a field called Comment of data type "Long Text", and I want to make an update query that adds a certain text, say "abc" + what is already in the field to all lines except those which don't already have this text. My query looks somthing like: Update tbl_a SET...

The above works with some simple test tables, but not with my real table/query. Maybe there is some kind of problem with uniqueness after all,.

Hi everyone, I'm using Access 2010. Whenever I try to make an update query based on another table/query I seem to get this error: Operation must use an updatable query. (Error 3073) Let's say I have two tables: TbA: Inv PO -------------- 1 2 2 5 TbB: Inv...

Thank you recyan. You understood correctly. Now I am feeling very stupid. :banghead: Note to self for future reference: don't try to work with Access 2-3am at night. ;) I don't know what happened really, but somehow I got the idea that the date field should be unique for some strange reason...

Hi, I am trying to make a database that will keep track of invoice backlogs for different locations within a company. Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like...

Hello, When I start my database normally, I'm not able to add fields, change chart type, etc, by right clicking in a pivot chart or table. It works when I open the database while holding down the shift key though, so I suppose there is an option in my database somewhere that I have changed...

Thanks, namliam. You're a genius. :)

Hello, I have a query that displays the difference between the number of scanned and processed invoices for each date (invoices scanned minus processed). Now I would like to make a query that for each date based on this shows the total backlog accumulated. Example table: The first column...

As it turns out, it was a crosstab query using the previously mentioned query that failed, not the one I pasted in the previous post (my mistake). By manually adding [Forms]![d3FormAging]![KPIDate] with datatype "Date" in the crosstab query parameters in accordance with the following link, it...

Thanks. With your instructions I was able to get the crosstab query to display all date ranges, the only problem now being that there is an empty line with a "null" location number and no entries. But I guess I will have to live with that. ;)

Hello, I have a function in a module that looks like this (it takes 2 dates as arguments): Function get_KPIScanAgeRange(in_ScanDate As Date, KPIDate As Date) As String Dim ret As String ret = "Invalid" ' return value, by default is because age is negative number Dim int_ScanAge As...

Now I have also run into another problem. I'm trying to adapt the above function and query to make it possible to show the results with reference to a selectable date also rather than today's date. I changed the function as follows (adding a second parameter to the function): Function...

I have now made a form with a subform displaying the cross tab query, and it's all fine, except one problem: If there are no open invoices in a date range, this date range will not exist as a source control and thus #Name? will be displayed instead in this column which doesn't look very nice. Is...

Thanks a lot plog! I tried what you said and made a cross-tab query, and it shows me the data pretty much as I want it, just that the columns are in the wrong order due to alphabetical sorting of numbers, but I can live with this. Again, big thanks!

Thanks for your answer. I didn't realise the degree of difficulty this involved, so if this would be too complicated, perhaps I could use some kind of Pivot table instead as what I really want is just a view displaying each location and the number of open invoices in each date range. Anyway, to...