MS Access Skip function
I am creating a database on MS Access currently and was wondering if there was a way to program it to automatically skip over certain fields based on an answer in a previous fields? ...
access-vba
Simple VBA Code Question
Hi, I am trying to bring back records from a table called CTN_LIST where the created date is no older than 90 days. The code is below (created_dt<=-90) Set PlatinumList = db.OpenRecordset("SELECT FORMATTED_CTN FROM CTN_LIST WHERE ((Status='Available') AND (Category='Platinum')) AND (In_Offer_List = True) AND (Created_DT<=-90);", dbO...
Access VBA Question
I'm trying to use the below code to bring back all records that are not older than 90 days from a table Set PlatinumList = db.OpenRecordset("SELECT FORMATTED_CTN FROM CTN_LIST WHERE ((Status='Available') AND (Category='Platinum')) AND (In_Offer_List = True) AND (DATEDIFF(day, Created_DT, current_date()< 90);", dbOpenSnapshot, dbReadOnly...
Access-VBA: Empty result for missing values in function?
Hi, I've got a question that probably has a pretty simple answer, but I didn't find a solution yet. Here's an example: Public Function abc(var_1 As Variant) As Single abc = 2 * var_1 End Function I want to call that function in an Access query. Values for 'var_1' are from a numeric (single) data field. Problem: Some the values for va...
IF Statement in VBA
I have a If statement as followed: If Weekday(Worksheets("Actual").Range("C1").Value) = vbSunday Then What this If statement does is create 3 emails with attachments and links of data from Saturday and Friday, to send if the date entered in the worksheet is a Sunday. If Sundays date is the first of a month i.e.(August 1, 2010), making...
Access VBA equivalent to a C# List<T>
I have a COM-visible object written in C# that accepts a list of string arrays. Could I send a Collection of string arrays from Access 2000 to this object and it work? If not, then what is the best way to send multiple string arrays to my C# object from Access 2000? ...
how to save data from ms acess front end to ms sql backend?
how to save data from ms acess front end to ms sql backend? ...
Strange Problem With VBA in Access SQL
Hi, I have written this VBA module for a school project in Access 2007, and it works fine in the Immediate Window in the Visual Basic Editor. However when I use it in a query (SQL) the value just doesn't show up. I have no idea why. Here is the module code: Option Compare Database Function LoopIngredients(itemName As String) As Long Di...
Access Database Security Question
Hi, I have a database in Access 2003 that I only want certain people to be able to access. In my database I have a table which lists the people that should be able to access the database. (Tbl_BIRT_Users). The table contains their name, ntlogin and email address. It also has an 'adminstrator' field. My question has two parts: 1 - On ...
How to insert a Please Wait form while a query is running
I want to display a "Please Wait" form while a 60+ second query is running. I have the query and the form made (easy enough), except the macro won't bring them together. Here is my autoexec macro: SetWarnings=False Open Wait Form Run the query & Display Results Close Wait Form The problem is that I can only see the outline of my wa...
Some bizarre behavior in MS Access. Please help.
I have defined three tables, Stores, InventoryItems and StoreItemRecords. My StoreItemRecords table has foreign key columns (StoreID, InventoryItemID) that "point to" the primary keys of the Stores (StoreID) and InventoryItems (InventoryItemID) records. The columns are named the same between the tables. If I run a query like this: SELE...
Execute Access 2007 Report and Export that Report to PDF Programmatically?
I have a great report in an Access 2007 Database and would like to automate the execution and exportation of that report. Is this possible and what would be your first guess at the method needed to pull it off? ...
Access's trim() function not recognized on another machine
I built a modest Access DB and asked a coworker to test it on her machine. To my surprise, an error message popped up, saying it didn't recognize Trim(). The query in question is a standard select query that pulls from a MSSQL Server DB. Why isn't it recognized on her machine and how do I get around this? (We both use MS Office 2003 an...
Excel 2007 ODBC data populates when opened via Windows Explorer, but not when opened via Access VBA
I have an Excel 2007 workbook that contains an ODBC data connection (to FoxPro, if that matters). The connection is set to "refresh data when opening the file." When I go into File Explorer and open the workbook, the data populates into the spreadsheet as it should. However, when I execute a function in Access VBA that opens the workboo...
Linked tables are sometimes locked MS Access VBA
Hi All, I have two MS Access files (.mdb) one is called "A" and the second one is called "B". A contains the database tables and B is read only and contains reports. B also contains Tables and are linked to the tables of A. When I run a report from B, the linked tables are locked and give me nothing. Is there any solution for that? Than...
VBA problem with String concatenation
I want to create a query like this : SQL = SQL + "Libelle = \"" + Me.Libelle + "\" AND " because if I have a problem with my data if I write like this : SQL = SQL + "Libelle = '" + Me.Libelle + "' AND " but there is a problem with the \" I'm used to use it in Java (I'm not a VBA developper :s) How to resolve it ? thnx ...
How to delete a record and assign an existing record in its place.
Hi there I'm wondering if anyone could help me out here. Is it possible when deleting a record to assign all instances of the deleted record to a new record? For example: For each project in a time planning database, i can assign a worker as team leader for each project. If there was a duplicate entry for a team leader i would need t...
Is there a way to override automatic record updating of Access 2007 forms created with Form Wizard?
I'm new to Access VBA, and have created a form using the Form Wizard that displays records in a table. That's a piece of cake. The behavior that I get with the form, though, is that updates to the records occur automatically when I move around records. What I'd like is to have the updates occur only when I click an "Update" button tha...
access performance, mysql nested functions
I have a table in access with about 180k records. I have a bunch of sql statements that I tested and work perfectly on a smaller version of my data. With access & that amount of records, things are pretty slow, but a sql statement with a Dconcat function I even let operate overnight didn't get even close to 1/4 way through. I assumed ...
How to Correctly Specify ATP 2.0 XIRR Function Call in Access-to-Excel Automation
If someone can help, I need some in properly defining some call parameters in an Access 2003 to Excel 2003 VBA problem. I'm trying to use the XIRR function in the ATP 2.0 Type Library from Access. I have referenced the ATP 2.0 Type Library in my Access project. Here is the relevant VBA code (with a little pseudocode) I'm using behind a f...