Search results

Calculated fields, as far as I know, have to go in a query. Basically, you're creating a View of your table that will include the calculated field, and you just use that as a table. So make a query that has all your table values, then put a calculated field in the query that multiplies the unit...

Bug in Access? Ok, this is very strange. I actually have three calculated "Totals" fields. The first two multiply different fields in the "trucks" table, and the third adds up those two totals. If I use Total1: nz(Sum(field1*field2),0) in the first one, Total2: nz(Sum(field3*field4),0) in the...

Bug in Access? Turn null values into zero... This should be easy. I have two tables, "Orders" and "Trucks", related by an order_id field. I run a query that returns all Orders records and only Trucks records that match an order_id, then I group by order_id. So I end up with a table something...

I was right! There were better ways.... Thanks :)

Sounds like it reapplies the filter when you save it. I may not understand what you're doing exactly, but I can give it a shot: You probably already know this, but since you know what the record number is, you can probably write some code into your save button that sends you to back to that...

Look up the DAY and WEEKDAY functions and see if they'll help you.

If for some reason that doesn't work, you can filter your entire recordset: Function filter_date() Dim strFilter As String strFilter = "order_date = #" & Me.picker.Value & "#" DoCmd.ApplyFilter , strFilter End Function I created a DatePicker object in this case that filtered...

I could be wrong, but I would think that if you're getting #name then your "Active" control is not a checkbox. If it's a text field, Access wouldn't know what to put in it if you only had a True/False value. I'd try something like IF (DLookup("[Active]","tablename","criteria) = True Then...

I've got a form that shows orders. An order has a field "invoice_number" that is null until that order has been put on an invoice. What's the best way to disallow edits to that order record (and its subforms) ony if there is a value in that invoice_number field, so that orders cannot be edited...

Thanks very much, to both of you - your explanations were quite good. In the end, I had to store my_date as a string and insert the # marks before and after it. If I store it as a date, the debug window shows that it has the # marks there already, but Access apparently ignores them. Very...

Thanks! Pat - thanks for the great advice. I didn't really explain the problem very well, but I can use a similar solution to the one you described - Should have thought of it before! The problem is that my order has many items, and sometimes I want to charge the Provincial Sales Tax on them...

Well, I tried that, and I got the same results - it screws up the date. There are probably better ways to do this, and definitely better database designs (Every time I redesign it, I learn something, but you have to get version 1 out the door someday...) Here's the code: Function...

Thanks Rich... I don't quite understand what you mean by "US Date Format" though - I thought that using "Short Date" as the format for that text field would work just fine. I'm now quite confused. I'm storing it this way because I have a series of subforms asking the user for data, which I put...

What happens when you go to File -> Import external data -> Link Tables, and then pick your data source? Does it show all the tables? ODBC is a pain, I'm learning. I have a Sybase ODBC connection set up that only works in Office97 -> OfficeXP doesn't like it. Very strange. Good luck...

Date turns into 12/30/1899 ?? I've got a form where, among other things, users enter a date in a text box. The box is formatted to MM/DD/YY. In my VBA code (Access2k) I've got Dim my_date As Date my_date = SubFrm_invoice_2!txt_invoice_date.Value Later on I go on to use an SQL statement...

A better way? I appreciate the response... I'm not yet a professional, experienced database designer but I'm learning fast, and still have another course to take on this stuff before I finish my degree. I would really appreciate any suggestions you might have on a better design for this type...

Hi there everyone, I've got records that are orders. Each order has about 10 possible items. Each item has a checkbox stating whether or not PST will be charged on the item for that particular order (it has to be done this way). I wanted to make a query that calculated the PST for each order...

Thanks everyone for the replies! Good suggestions. I think I'll go with jatfill's suggestion and use the subforms - seems like a good idea, and I can keep all the info in variables until the user decides to save changes =) Too bad there's no wizard-building wizard for Access 2000.....

Hi all, I want to make my own little wizard to create invoices. The first form should ask for an invoice number and have "Next" and "Cancel" buttons. The next form should ask for a PO number, and the next form should display a list of orders to add to the invoice. The last form will give the...

Yup, thanks - turns out that was the problem. Took a while to find it though - developing Access apps can easily turn your program into a spaghetti-like code without careful attention to design.