Search results

When you bring the RGU field into your query, use the nz function to change nulls to zeroes: SELECT nz(RGU, 0) as Alias1 This will give you zeroes in your query dataset, but won't update the actual table cells--they will remain as nulls. Duluter

Perhaps I am misreading your IIF statement, but isn't this: set h.price = iif(h.FX=1, h.amount/h.price, h.fx*(h.amount/h.price)) identical to this: set h.price = h.fx*(h.amount/h.price) ? Or is h.FX different from h.fx? Duluter

datAdrenaline: Good info. Thank you for posting. I so much wish I could migrate our tables to SQLServer and keep all our front ends in Access. I work for a small company, so this is probably not a reality in the near future. Still, splitting the database and backing up regularly should do the...

I see. I'm not too sure because I've only ever left them as the default (inner join). My hunch is that Access uses this join type as the default if you bring these tables into a query using the query design window. If that's the case, if you usually want inner joins, then leave them as is...

Thanks, Banana. I am in complete agreement with you. I will split the db as soon as I can. But I still have an unsatisfied feeling because I don't really know if what I did caused the problem or not. It seems very strange to me that accessing an already open db via ADO would cause all of the...

The code is in Outlook. The database is opened very infrequently, so I figured I could get away with it. I will split it. Is this a known issue with ADO and Access? Is it documented somewhere that the database could get messed up in this type of situation? Thanks, Duluter

1. The database is not split. 2. In VBA, using a connection string, like this: Dim con As ADODB.Connection Dim rs1 As ADODB.Recordset Dim sConString As String Dim sSQLString As String Set con = New ADODB.Connection Set rs1 = New ADODB.Recordset sConString = "Provider=Microsoft.Jet.OLEDB.4.0...

Hi everyone. I was just wondering, is it a problem to try and establish a connection and grab a recordset via ADO to an Access database that someone else has open? Could you hurt the database if you try to do this? The reason I ask is because I was writing a macro in Outlook this morning. My...

You may want to enforce Referential Integrity in some of your joins. It helps make sure that you don't delete a record in your parent table if that record has matching records in the child table. For example, it won't let you delete Customer A from the Customers table if Customer A has orders in...

Here's a Microsoft support article that will help: http://support.microsoft.com/kb/287658 Try Method 3 in the article. Duluter

On your certificate form, are you actually saving the certificate data to a table, or are you just creating a certificate that will be printed? Duluter

If I understand correctly, you have customers to whom you are issuing certificates of some kind? I'm thinking that you would use a form to collect the info you need, such as the customer name and whether or not you want your signature to appear on the certificate. On that form, you would have a...

As you mentioned, you are going to have trouble performing an aggregate function if your Site field has extra, variable info to the right of the main site number. Instead of pulling in the entire Site field from the table, you could try pulling in just the main site number. Am I correct in...

Hmm, it seems like what I'm after would probably be a real pain to implement. If you switch around the groupings and sort orders, then you'd need to reassign which fields get placed in which controls in the report body. And you'd also probably need to move those controls to different locations...

Hi. I searched the archive through Google, but didn't find what I was looking for (though it seems certain that this question has been asked before). I have four reports, all generated from the same query. The only differences among the reports is the way the data is grouped. Is it best to...

Are you asking if you can base your query on another query, rather than basing your query directly on a table? Duluter

The nz function will return either the value that you feed into it, or, if that value is null, then the function returns whatever you specify. In this case, I specified for it to be zero. I myself am still learning about the Access environment. Perhaps others here know a good book on Access...

Hmm, I'm not really sure what I was thinking earlier with the union query. INSERT INTO t1 (Streets) SELECT NewStreets FROM t2 WHERE NewStreets not in (SELECT t1.Streets FROM t1); Does this work? Duluter

I created a left join query on your Sales_Reps table and your qry_Electronics_Return query to add in the employees who did not have electronics returns, giving these employees a return count of zero for the selected month: SELECT Sales_Reps.UserID, nz([Total_electronics_Return],0) AS...

Hi. Let's try this: First, create a union query that joins all of the street names into one result set: SELECT t1.Streets as AllStreets FROM t1 UNION SELECT t2.Streets as AllStreets FROM t2; Call this Qry1. Now we're going to create an insert query to insert these streets into your t1...