Search results

Sorry for the confusion, I think I should make my case more clear, Since the column change I made is from col3 to col5, so I want to develop a loop such as : With rst .MoveLast .MoveFirst lRecordCount = .RecordCount If lRecordCount > 0 Then vResults...

Thank you for the iif function, but in my case, I need to modify the query later, since I have a very large database, lots of "NA" in the data, I ll need to make sure if one row in col 4 is "NA" and col3 is c5, then there is no data selection in col3 has the same value c5, otherwise, all the...

Hi baldy, Thank you for your reply. So an example of my problem is that I have an initial table with 6 columns, and I need to develop a new query which contains the first 3 columns from the initial table, and the rest field is depend on my sql. the first sql is to check the 4th columns from...

Hi, I am working on write Sql in VBA and I want to save this query into a array table. Since I will change my Sql later,so the array size can not be defined now. I haven't done any code with array before, can anyone give me some example or hints on how to do that? Thanks!

I don't want to creat more query though, I will think about it, Thanks

OK, I'm currently using Access 2002 Here is the data Query A id col_1 col_2 R1 UA UC R2 UT UW R3 UBC SFU R4 UL MacE R5 UV UNBC R6 Queen UO ...... Query B id col_3 R2 ON R3 BC R4 AB R6 ON The result in QueryA I want is like below id col_1 col_2 col_3 R1...

Here is the sample: I have query A like below: id col_1 col_2 1 2 3 4 5 ... and query B like below: id col_3 2 3 5 6 ... Now I want to write VBA code to insert the entire column of col_3 from query B into query A, where the B.id=A.id Data type on col_1,2,3 are text Can you help me to...

Hi Dear all, I have Query A and Query B, and I want to insert a new column to A from the exist column in B, here is my code: "INSERT INTO A (col_3) VALUES (SELECT col_3 FROM B " & _ "WHERE A.id = B.id ;" Please provide me the correct way to do that, thanks

Thank you so much! It finally worked :)

Thanks! I make it worked :)

Hi All, I am working on my query criteria on VBA now but I keep getting the snytex error from my query SQL, can anyone check it for me? Thanks! strSQL = "SELECT weeks.Year, week_catch.Week, Species.Species, Species.SpeciesRef, week_catch.Catch, Area.Zone, Area.AreaCode, Area.TotalArea...

Thank you! I get what you mean, just want to be clear that the SpeciesTB in your code is represent the table where I built my list box or the final query that all the three field would be in?

yes! I saw that, but that is the part that I'm not so clear... how did the sql pass to my query "SelectionSum" which contains the selection of year and area zone? I need the data selection from these three field (year, species and AreaZone), year is input into the form and AreaZone has three...

Hi, Sorry for the confusion, I shouldn't mention the other form...haha Well, the layer of my form is: ----------------------------------------------------------------------- Choose year range (enter the year) Choose species (select from Mutiple list box) Choose Area Zone (Zone1, Zone2, Zone3)...

Hi,Thanks! But my list box is multiple select which I set the property to simple already, but I don't want to click any button after I select from the list box. I need to pass it to a query criteria without click any button on my form, do you know how to connect my selection with the query? I...

Hi all, I have a multi select list box called "Spc_Slc" and want to pass the selected value as criteria to the field called "Species" in my query. My form is called Catch_Selection, so after I built my list box with the RowSource from my original table SpeciesTB, I created a query with all the...

Got it! Thank you!

Hi all, just quick question this is my sql on VBA code Sql1 ="Select [SmallArea] From [AreaQuery1] WHERE [SmallArea]="NA";" I try to find any record in SmallArea that contain "NA", it give me the compile error of "expected: end of statement" Can anyone fix it for me? Thanks!

Totally worked! Thanks a lot!

Thanks Beetle, it worked, but I just got another problem that there is duplicate data in the field A2&A3, when I get my list box, how do I keep all the data in field A1&A2 and then eliminate the duplicates while the list box shows field A2&A3?