Search results

Actually, you're close. Dates are just numbers, being the number of days since the end of 1899 or something. So Now() - [Date Entered] will give you roughly the right answer. Just be careful of times and rounding, as you might get an extra day if it is after 12 midday. Use the Fix function to...

If the spreadsheet doesn't change in name or format, then you might find it easier to create a link to it and run a report from it directly.

Or =Year(Form!InvoiceMonth)

Try =Year(Form.InvoiceMonth)

Why wouldn't your code which makes the changes just not do anything if it had already been run. So if one of the things your code does is to add a field to a table for example, the code, before running would check that it isn't there already. So it will only ever run once. But if making updates...

Actually I meant in tblResponseOptions make ResponseOptionID and ResponseValue the primary key. The only thing really missing is a ResponseType field, which would determine how the options were treated. It would apply to a single ResponseOptionID so would need to be in another table, which...

Yeah I get it. That's why you test for M<7 on a separate criteria line. So any date in a month earlier than July will always be returned, as well as any date in July which is up to the 12th, from the second criteria line. There's also the format function which returns a day's position in the...

Yep, that looks pretty much exactly how I'd be doing it. Except that you will need more than one possible option for questions, so ResponseOptionID can't be a primary key of tblResponseOptions. ResponseValue would need to be part of the primary key.

I'd never do the above, unless I knew I was never using the database again, and never building a survey again. I'd definitely have a questions table with one row per questions. It would need a primary key which could also serve to order the questions, or those could be independent. Either way...

That's a good question, as it isn't an easy task. We either need to use VB code or to use a query trick which won't really work for even medium size datasets.

Well then, one way would be to add two fields to your query. The first would be something like M: Month([DateField]) and the other D: Day([DateField]). This obviously assumes your dates are in a field called DateField. Run that to see the results these additions give you, so you have an idea...

Are you saying that you want to see records from January 1st until July 12th regardless of the year?

It isn't easy to get a number like that a query, no. Do you want it stored permanently in a table? Or visible on a report?

I'm with Peter here. Get a fresh DB happening and bring your objects in. Check that CurrentDB.name is working at various stages. All the stuff except your dodgy form to start with. Then maybe bring it in and see if it still works. Something is wrong with your form, if not your whole db.

Create a SELECT query with your field and another alongside it with the following Field value NewVal: Left(Spending, Len(Spending)-2) & "00" Run that to check that the NewValue field looks like you want Spending to look after you update it. Doing this is always a good idea, especially while...

Let's just focus on one of your dates. You can extrapolate it for the other. First you need to know what month that is in, so in your query you need a field StartMonth which returns the full month you want to match. Get that to work first. I suggest StartMonth: format(cdate([StartDate])...

Maybe use Me.Filter7.Value <> "" in your if statement? Hard to help as you don't say what or where the error is? Take the square brackets out of your RowSouce as well, or maybe just reduce it to "Is Null" or "Is Not Null" and bring the field name back to VBA. Or just have a one-column combo...

Is Spending a text field? Will it always have four characters? Or is it a number and you want to round it down to the nearest 100?

In your original query you need to either remove "DespatchRef, SupplierRef " from the SELECT clause, or add a "GROUP BY DespatchRef, SupplierRef" clause. Seeing as you are restricting these fields to values you already know there is no point returning them, so go with the first option. But if...

str="Free slot start: 13:30:00 End of Free slot: 15:00:00" intPos = instr(str, ":") dtm1 = cdate(mid(str,intPos+1, 9)) intPos = instr(intPos+9, str, ":") dtm2 = cdate(mid(str,intPos+1)) Assumes you'll always have the hh:nn:ss format with 8 characters. Could make it more robust by looking for...