Search results

In that case you'd pass it as a string expresssion and then concatenate its value into a string expression, which you'd assign to the control's RowSource property e.g. with the following function in a form's module: Private GetRowSource(strDateColumn AS String, strTable AS String) As String...

What you currently have is really a spreadsheet masquerading as a table. A relational database is very different from a spreadsheet however, being made up of multiple related tables, each of which models an entity type in that part of the real world being modelled by the database, with each...

You are correct on this point. You have a type hierarchy, in which individuals and busineses are sub-types of type contacts. A sub-type is characterised by sharing all attributes of its (super) type, but not those of other sub-types. The one change I'd suggest making would be to drop the...

To show the first three characters of the client's name in upper case extend the expression I posted earlier as below: UCase(Left(ClientName,3)) & Format(ClientID,"0000") & "-" & IIf(ClientType = "Individual", "IND","BIZ")

Isn't that what the expression I posted does? Do not assign the result to a primary key column, however. Compute it on the fly.

Can't you simply use the following expression in a computed control in a form or report, or a computed column in a query: Left(ClientName,3) & Format(ClientID,"0000") & "-" & IIf(ClientType = "Individual", "IND","BIZ") where ClientID is the autonumber primary key of the table. Note BTW that I...

I've amended my file to prevent double-bookings of one or more tables: The following line of code was used to apply a CHECK CONSTRAINT to the TableBookings table: CurrentProject.Connection.Execute "ALTER TABLE TableBookings ADD CONSTRAINT PreventDoubleBooking CHECK((SELECT COUNT(*) FROM...

Malcolm, I'm attaching the file from which the form I illustrated in my last post was taken. This enables a single booking for multiple tables to be made. You'll see that the file also includes the following query: SELECT COUNT(*) FROM Bookings AS B1 INNER JOIN TableBookings AS...

I would use an unbound form to make bookings. In the image below a a booking has previously been made for tables 5 and 6 from 18:00 to 18:59, and a further one hour booking for the same tables is being made for the same tables from 19:15 to 20:14. I need to amend the code to confirm the...

If you want to be able to make a single booking for multiple tables, then you should model the many to many relationship type between Tables and Bookings like this: You can then return the available tables for each time slot with the following query: SELECT Tables.TableNo...

Don't you mean 25.25, Duane? That would require the result of the summation to be multiplied by 24 as summing a set of values of date/time data type would result in the total number of days and fractions thereof. However, is 36.0833 as easy to visualise as 36:05? By all means do the maths in...

The attached file illustrates a number of time arithmetic functions. In your case simply call the TimeElapsed function in your query: SELECT tblDuties.PayPeriod, TimeElapsed(Sum(tblDuties.Overtime),"nn:ss") AS SumOfOvertime FROM tblDuties GROUP BY tblDuties.PayPeriod ORDER BY...

Exactly. If, on a system using UK date format, a date literal makes no sense in US format, which is the case with #29/12/2025#, Access will interpret it as the intended date, but if it does make sense, as with #04/07/2025# it will interpret it incorrectly as 7th April, which is not the...

Not so. A date literal must be in US date format or an otherwise internationally unambiguous format such as the ISO standard of YYYY-MM-DD Note that the standard uses case to differentiate months and minutes, whereas Access use m for the former and n for the latter. The CDate function, on...

That part needs to be evaluated first, outside the quotes delimiting the literal string, and its return value then concatenated to the latter. See post #18 above.

The criteria argument of the DSum function call should be a string expression: =DSum("Covers","tbl_Net_RestaurantBookings", MyToday("CheckIn") & " And Status Is Null") Note how the criteria argument now evaluates to the string expression returned by the MyToday function concatenated to the...

Nor was any mention made of not using them, but I wouldn't really expect there to be. I found that I nearly always had to allow the detail section to grow. That could be because, in my line of work, we do use quite long winded scientific terminology. Until the OP provides more info I'd play...

But what if some rows wrap to two or more lines?

You don't need to return any blank rows. You can simply force a page break in the detail section after each 20th row. Firstly add a page break control to the bottom of the Detail section. Next declare a module level variable in the report's module's Declarations area. The following is from...

To identify tables which are available for the whole of any given date/time range you firstly need to identify the intersections of two sets. You can then exclude those results from the available tables/times. This requires firstly an auxiliary calendar table, which is simply a table of all...