Append query with IF or WHERE

[Anil Bagga]

I have a master table with 50+ items where we store price changes. Therefore the table has prices and date of updation

Majority of the time all items are updated monthly and all at the same time. Therefore I created a temp table linked to a continuous form for ease of updation. The idea is to use an append query to append these values in the temp table to the main table and then empty the temp table

The command button vba is

[DoCmd.OpenQuery “qryRM-MB-append”
CurrentDb.Execute "DELETE * FROM [tblRM-MB-Price-tmp]"]

(being new to the forum I could not understand/follow the advise of using code tags)

The problem is that not all items on the form have prices and therefore items with null prices are appended to the price table.

Question : How can we have a IF or WHERE condition with the append query e.g DoCmd.OpenQuery “qryRM-MB-append” WHERE [landedrate]>0.

[Welshgasman]

Surely the query would have that criteria?

[Micron]

You copy your code, click the # on post toolbar, cursor will be within 2 code tags, you paste (ctrl+v) - and done.
Maybe to add a bit to the last post, modify your append query to restrict appends to where price field Is Not Null? You can test an action query by switching from SQL view to datasheet view. Sometimes that can be confusing though as what you'll see is what is going to be affected, not necessarily how it will be affected. At least it should show 1 of 3 things: it errors, it does nothing, it does something. If it errs, it usually returns to sql view and highlights the offending part but not always.

[Welshgasman]

You copy your code, click the # on post toolbar, cursor will be within 2 code tags, you paste (ctrl+v) - and done.
Maybe to add a bit to the last post, modify your append query to restrict appends to where price field Is Not Null? You can test an action query by switching from SQL view to datasheet view. Sometimes that can be confusing though as what you'll see is what is going to be affected, not necessarily how it will be affected. At least it should show 1 of 3 things: it errors, it does nothing, it does something. If it errs, it usually returns to sql view and highlights the offending part but not always.

@Micron,
O/P has over 140 posts on another forum, so that much of a newbie?

[Micron]

@Micron,
O/P has over 140 posts on another forum, so that much of a newbie?

I think you and I know post count is no measure of capability. If you're referring to AWF, I wouldn't know anyway, right? There was once a guy on here who continued to ask the most basic questions and bloat his threads to the point where the forum gave him VIP status because it's based on post count. So I'm afraid that means not much to me.

[Welshgasman]

I think you and I know post count is no measure of capability. If you're referring to AWF, I wouldn't know anyway, right? There was once a guy on here who continued to ask the most basic questions and bloat his threads to the point where the forum gave him VIP status because it's based on post count. So I'm afraid that means not much to me.

Of that I agree, however by then they should at least be aware of code tags and would likely have been advised on the forum?

[Micron]

So you're calling me out for explaining to the OP how to use code tags?? Really?

[Welshgasman]

So you're calling me out for explaining to the OP how to use code tags?? Really?

No? nothing of the sort. Could not be further from that.?
I am just saying that the O/P should be aware of code tags by now, that is all, and was advising you of the fact?

It does not hurt to repeat it I admit, but perhaps it's falling on deaf ears?

[Micron]

Would have been better if you had directed your comment to the OP then, not me. As in "Joe, you have enough posts at the other forum and should know enough to use code tags by now." Sorry for the misinterpretation or whatever it was. Now post count here over tags exceeds posts for the issue.

[Welshgasman]

O/P hasn't responded to my question about the criteria in the query, so I do not think it is an issue.