sql - Validating records in one table based on data in another table -


this question offshoot of this question here. using loop loop through recordset based on master table, able records returned inline sql statement show. however, when put validation code in loop, valid employees being reported invalid , vice versa. 

public function validemployee(empid string)  dim dbs dao.database dim rs dao.recordset dim sqlstring string set dbs = currentdb  sqlstring = "select [employeeid] [master] [employeestatus] = 'terminated'"  set rs = dbs.openrecordset(sqlstring) rs.movelast rs.movefirst // obtain accurate count of records in recordset  if not (rs.bof , rs.eof) // verify recordset not empty     until rs.eof         if instr(1, rs.fields("employeeid"), empid, vbtextcompare) = 0             validemployee = "valid employee"         else             validemployee = "employee" & empid & "is invalid"             exit         end if     loop     rs.movenext end if

some of steps have tried include:

  • checking leading or trailing spaces in field names
  • validating values of empid , rs.fields("employeeid") via debug.print
  • checking syntax errors both in sql , in vba
  • quotation / escaping of string literals in sql statement

i feel problem in way wrote instr() comparison. comparison using single record without loop works fine.

you don't need loop on of records find match: database can handle that.

public function validemployee(empid string)      dim rs dao.recordset     dim sqlstring string      'add single-quotes around empid if it's not numeric field     sqlstring = "select count([employeeid]) num [master] " & _                 " [employeestatus] = 'terminated' , " & _                 " [employeeid] = " & empid      set rs = currentdb.openrecordset(sqlstring)     validemployee = rs.fields("num").value>0 end if

Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more