Search results

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...

You can return the full address as a concatenated string expression in one query, and then base another query on that query rather than the base table to reduce the complexity. When concatenating values you might find the following function useful. It's an amended version of the CanShrinkLines...

I should have made it clear that this only applies to times under 24 hours. A value such as 31:05 returned in the example in my last post cannot be recognized directly as a time either in Excel or Access. When undertaking time arithmetic every step in a routine should be undertaken on the...

Excel will still treat the expression as a time, and it will be possible to do time arithmetic on it, as with your example, subject to the same proviso that applies in Access, that the result must be less then 24 hours to be intelligible. Otherwise the result, formatted as hh:nn will be the...