Search results

I've got 2 crosstab queries that both work, but I'd like to combine them together. The first shows the total time that various vehicles were used for at each base: TRANSFORM Sum(Cdbl(Nz(M.dtTripTime, 0))) AS SheetTime SELECT M.lngVehicleID ,Sum(Cdbl(Nz(M.dtTripTime, 0))) AS Total FROM...

This is on a sharepoint web database... I'm trying to run a data macro which simply retrieves field values. When I run it from within Access, it gives no errors. However, whenever I try to run it on Sharepoint it throws up a 3875 error. The only information I can find on that error is...

Hi all, I'm trying to return the most recent value from a table, before today's date. As this is a web database, I believe I need to use a data macro to achieve it (which I'm very new to). So far, I have: Look Up A Record In tblPrices Where Condition = [tblPrices].[dtPriceDate] <= Date()...

Hello all, I have a UDF in a module which is the controlsource of multiple textboxes. In addition to setting the value of the textbox, I'd like the UDF to set the fore and back colors rather than use conditional formatting (I'm attempting to speed up a very slow report). However, I can't find...

I'm trying to create a report for any given month which shows each employee and where they will be on each day of that month (Base1, Base2, Base3, Base4, Vacation). The employees are stored in tblEmployees The movements of the employees are stored in tblMovements My current method works, but...

Hello all, I'm struggling to come up with a recordsource for a report in the following circumstances: Employees work on Shifts, each shift has multiple deliveries, each delivery is to a certain district. What I need is a report grouped by district, showing the employees who have NOT made a...

*Solved* Problems keeping leading zeros Hello all, I have a problem trying to keep leading zeros. The entries in a text box can be numbers or text, and are not of a fixed length. I want to store the entry into a string variable, which ultimately gets used in a SQL INSERT statement (into a...

Hello all, I have a split Access 2010 database with plenty of VBA. I've been reading up on my options for sharing this over the internet (cheaply and easily, without losing the VBA, and probably without the need for any concurrent users)... am I correct in surmising that the only sensible...

I have a function which has several optional arguments. These arguments are either forms or controls. I need to test for how many of the optional arguments were passed. I can't use IsMissing because they aren't Variants. If I make the optional arguments variants I get a "ByRef argument type...

Hello all, I've been trying to filter and sort a continuous form on the basis of the value of a textbox in each row. The textbox's control source is a VBA function, which is running an SQL statement. This has not been very successful! Before I chase my tail even further on this one, am I...

Hello all, I am looking for ideas for an alternative to datasheets to achieve a continuous subform within a continuous form. I'm steering away from datasheets if at all possible because of their behaviour within a navigation control (persistent save prompts). The basic set up is: Each 24 hour...

I am having some strange results using orderby on a form. The set up is a simple combo box of column headings, with some vba to order the form according to the appropriate heading selected. This works fine for the fields with names, numbers and dates. But I am getting some strange results when...

Hello all, I'm struggling to get an SQL statement that works. Most of my (very limited) SQL knowledge comes from using query design and then switching to SQL view. That has got me through most things, but this current table design is stumping me. I've attached a simplified copy of the table...

Hello all, I'm struggling to convert an SQL statement to DSum. The table setup is as follows: DaySheets (Table 1) which contain multiple Shifts (Table 2) which are worked by various Employees (Table 3). The SQL statement in question is: SELECT DaySheets.DSdate, Sum(Shifts.ShiftDuration)*24...

I was wondering if anybody had a solution to the following problem... I have found it discussed several times on various forums but not a solution. Access 2010, using a Navigation Form (the problem does not replicate outside of a navigation form) If a change is made to a subdatasheet (for...

Hello all, Table 1 and Table 2 are joined. I have a loop for outputting reports of all the records in Table 1, showing their linked data in Table 2. The problem was whenever there were no linked records in Table 2 I got a 2427 error. So I decided to add a Dcount into the loop to check if...

Hello, I currently use the following function in a subform to successfully retrieve the previous record's value (a date): Function GetPreviousValue(frm As Form, strField As String) As Variant On Error GoTo Err_GetPrevValue Dim rst As Recordset Set rst = frm.RecordsetClone...

Hello, I'd like to know how I go about the following: Each station is allocated a vehicle. That vehicle is then used on call outs. The table design is therefore as follows: tblVehicle vehicleID (primary key) tblStation stationID (primary key) vehicleID (foreign key from tblVehicle)...

Hello, I have a continuous subform listing each employee which is based on a SQL statement, which changes depending upon the toggle switch located in the subform header. I have this working OK. I now wish to add another field to the subform which will be based on a joined table. I am...

Hello all, I'm hoping for some more pointers on table design as I start to build my database up. The relevant parts of the database for my current problem area are: tblSHIFT - a table recording each workshift performed. This table includes the following fields: ShiftID - primary key...