mas_oyama

I am doing a Access application that needs, in one of my forms, detect wich user is accessing it. We have multiple DRVs(Directeur régionnal des ventes , wich means Regional Sales Director... but i dont think that "director" is the right word in english... anyway), wich supervise diffrent stores.The form I am working on is a kind of query executor. Theres a treeview with a bunch of Stored Procs in it, when you click on one, the associated controls show up, these controls are the parameters of the stored proc, and when you click on apply, the query is executed and the results are shown in a JanusGrid(a grid basically... but with more options and all... its an ActiveX control). the there is buttons and textbox where you can decide to export the data in excel, either in a plain document, or in a template, and you can ask it to start at a desired location in the excel worksheet, and you can ask to skip some columns(useful when you export in a template, and you need to jump over a "totals" column and such... its pretty cool. anyway, back to the point.the fact is, each DRV supervise a couple of stores. So m. DRV #1 supervise store #1, 7 and 43, while DRV #2 supervise stores #2, 4, 54 and 102 for example. So i need to detect wich user is accessing the form so i can show its name on the top of the form, and so i can pass it as a parameter to my Stored Proc so only the shops he supervises only. not all the shops.I'm working on a .adp database, in ADODB... i know some functions arent the same in .adp and .mdb, so i specify it...thanks!

i could be sayin something totally stupid, i havent tried before i post.. but if you say between 'a' and 'b'+1, would it show up the b results? in that case, doing between 'y' and 'z'+1, then the Z results would show up...i pulled that out of my butt, i'm not even sure if SQL accepts that you zo 'z' + 1... but its worth trying

i found a way, but its damn ugly! i had to write these 3 subs: (i know the name of the txt box doens match what i said earlier, i'm in a fake, testing form. Private Sub txtPath_Enter() VarString= "" txtPath = ""End SubPrivate Sub txtPath_KeyPress(KeyAscii As Integer) On Error Resume Next Select Case KeyAscii Case 8 'Bacspace VarString= "" txtPath = "" 'VarString= Left(VarString, Len(VarString) - 1) Case 13 'Enter Case Else VarString= VarString& "" & Chr(KeyAscii) End SelectEnd SubPrivate Sub txtPath_Exit(Cancel As Integer) Call MsgBox("""" + VarString+ """")End Sub VarStringis a global string variable. thats not really pretty: as soon as i enter the txtbox, i clear both the txtbox and the variable, to be sure the user start over... you'll understand why if you test. the same goes if i press backspace : if your not at end of the textbox, i cant posibly know where you are(so where to delete a character from the string). anyway, that barely works... if you type text somewhere else than at the end(if you moved with the arrows), the text will still be appended at the end of the variable... lol. thats not right!

yeah well, for my part, i dont know exactly what most of these abbreviations stands for. but its not only about what does it stand for... its more about what it is! i dont know what php stands for, and i dont care! the fact is that i know what it php, and i'm not bad in developping in php. for my part, i think this is what is important(not the fact that i'm good in php... i mean the fact that i know what it it...). you cant ask for someone to be good in all these languages, but you can ask them to at least know what it is... if you ask someone what is an DNS? and they answer that its an old programming language used in the 70s, there is a problem with that candidate. but if they answer that "it is what translatethe "name" of a website into a ip adress", then i think that it should be good enough... its no big deal if he dont know that it is actually callde a Domain Name Server

when i exit a textbox, in vba, it automatically remove the spaces at the end of a textbox... for example, if i type "Thats stupid "(without the quotes...), as soon as i exit the textbox, it is converted to "Thats stupid" (without quotes... duh... and without the spaces i typed). is there a way to keep those spaces? i'm developping a utility to rename files in batch, and if, for example, i want to replace all the spaces by underlines, i want to type " "(without quotes) in txtContaining, and "_"(again, without quotes...) in txtReplaceBy, so when i hit btnProcess, every file in the specified directory will be renamed, by replacing spaces by underline. the whole thing is done, i can replaces "a" for "b", or anything like that, but i can not replace spaces by something, or something by spaces, cause the stupid ****ers at microsoft decided that i cant end a text box by " "...

oh well. you guys will have to wait until tomorrow to know if my front end solution is gonna work! I've been slowed down by something else(the way we are going to implement this, i had to convert the way i fill my treeview control with the procedures to a recursive procedure, it wasnt alredy recursive, and to do that, i had to change the stucture of 2 tables, so i took more than just 5 minutes!) and i we went to play badminton during lunch so i actully lost an hour and a half of work today! lol so i'll finish this tomorrow instead!

Ok thats alright. i think i found a front end solution... i'll try this out and i'll post again this afternoon to say if it worked or not

yeah, but the Select statement is pretty huge. also, if i that, i would have 3 select statements into 1 query and the 3 statements would be soo much similar... actually, doing that would be the same thing as splitting the 3 existing queries into the 18 queries i was talking about... what i want is to do some dynamic SQL( i just learned it does exists, but i dont know how... qnd sadly the tutorials on w3schools.com dont have any dynamic sql lessons)

i work on an access projet. i have to make a screen that will be used to execute some queries and that will show the results properly. Now i have to integrate another screen that was used to execute 3 specific queries. these queries had 3 view each, and now i have to incorporate 2 display types for each of these, so i'm at 18 queries(3 main queries * 3 views * 2 display types = 18 queries), and that makes no sense since all the queries would be practically the same. the way it was done in the past is that there was 3 queries, that searched for more than what the were to show, and then, depending on the view, some fields wer hidden or shown.but now its a whole another story: i dont work with the same type of control. Its not a subform like it was. now its a JanusGrid(an ActiveX control that the company i work for bought), and i dont think i can just go and hide some fields from that. and also, my screen my be exempted of any hard coding! For example, my controls are called txt1, txt2, cbo1, cbo2, txtDate1, txtDate2, etc. so that way, i can refer to these fields in my configuration table so i can associate them with the parameters of the queries and all. Anyway, its a great way to do what I have to do, and it works really really well.but like i said, now its a whole another story, cause since i cant just go and make a combobox that were specifically made for these specific queries, and i cant just hardcode what is supposed to happen when i change the selected item in my cboBox containing the views and the display types, i assume that all this would need to be done in my queries. so, on to the point. I would to do something like this, but in TSQL rather than in VBA: dim strSQL as stringstrSQL = "Select"if cbo1 = view1 then 'cbo1 would be the combobox containing the views list for example strSQL = strSQL & ... ' some fieldselseif cbo1 = view2 strSQL = strSQL & ... ' some other filedsend if ' and then execute the query So i would like to do that, but in TSQL... is it even possible?

ok. lol i posted this here cause i've coded a little bit of VB.NET, and since now i do some VBA in access, i thought it belongs in the .NET forum more than in the SQL forum!

hum. well. no, not really. for 2 reasons:1) its not really in the code. its just in the textBox proprieties(sp? lol sorry for my bad english) on ControlSource.2) I can't go and put the code on the net... i'm in a company, and i've signed a contract, in witch it says i cant give the codes to ppl. i did post some code in another thread, but it was unfinished code, and i was the one who wrote that...edit: what i search actually is a kind of isnull() function that i can put in the ControlSource propriety. ... like isnull([subTrx]![txtNbTrx], 0). i dont think i could use isnull there though...

ok, i have a textbox wich control source is =[subTrx]![txtNbTrx] . so it is supposed to show the value of another textbox, in the subform. the problem is that when i load the form, the query is not yet executed(its a search screen), so there is nothing yet in subtrx.txtnbtrx, so the textbox shows "#Erreur" is there a way to tell it to show "0" when the other textbox is empty?oh and yeah, i know that its not the best way to display the quantity of rows in the subform, i'm not the one who did that like that... i'm just trying to patch this up!

haha yeah... honestly i just want to get away from it... lol i passed a whole day of labor for a mere 12 lines of code... lol i feel pretty stupid right now. lol. i did the 2 projects that were supposed to take me about 10 weeks in only 3 weeks, and that impressed my boss a lot, but then i have to put a whole day in 12 stupid lines of code, which arent difficult after all! anyway. now it works. so it will stay like it is. loloh and again, thx a lot for the help.

i dont think i could, since i need to scan the whole table... doing udf_SubstitutePipes(Comment, Replacement1, Replacement2) would do one row. but anyway, once the query will be created, no one will have to go back in it.oh and thx for the isnull() function... i did something else that wasnt really pretty : lolanyway, thx for the info, it has been really useful

oh and Query analyser is a tool made by micro$oft. basically, it is used to test you queries before you actually implent them. it can also be used to actually make your stored proc. by adding alter function at the beginning of your code.and no, i cant write stored procedures in that specific Database. i can in another db, but not on that one. but when my query will be finished, i'll give the code to my suppervisor, and he'll create it from his login

oh crap! thx! i really need to do this in SQL though. but i've got some help from my boss.i'll explain the situation. i'm doint my stage right now, and although we did a lot of SQL at school, we didnt do something like that at all. but i went to see my supervisor(my boss), and he gave me a hand. i had it almost completely done by myself, but something wasnt working. since i was on this thing since 7:30AM (its currently 2:15PM), i wasnt able to see my mistake, wich was really stupid : a damn coma that i misplaced. lol. anyway, so now its almost working. heres the code i had to do: select Result = Part1 + Value1 + Part2 + Value2 + Part3from tbStockDetailComment dtlCmtright JOIN (SELECT StockCommentID, --DelPos1 = CharIndex('|', Comment), --DelPos2 = CharIndex('|', Comment, CharIndex('|', Comment) + 1), Part1 = CASE WHEN CharIndex('|', Comment) = 0 THEN Comment ELSE Left(Comment, CharIndex('|', Comment) - 1) END, Part2 = CASE WHEN CharIndex('|', Comment, CharIndex('|', Comment) + 1) > 0 THEN Substring(Comment, CharIndex('|', Comment) + 1, CharIndex('|', Comment, CharIndex('|', Comment) + 1) - CharIndex('|', Comment) - 1) ELSE '' END + CASE WHEN (CharIndex('|', Comment) > 0) AND (CharIndex('|', Comment, CharIndex('|', Comment) + 1) = 0) THEN Right(Comment, Len(Comment) - CharIndex('|', Comment)) ELSE '' END, Part3 = CASE WHEN CharIndex('|', Comment, CharIndex('|', Comment) + 1) > 0 THEN Right(Comment, Len(Comment) - CharIndex('|', Comment, CharIndex('|', Comment) + 1)) ELSE '' ENDFROM dbo.tbStockComment) cmt on dtlCmt.StockCommentID = cmt.StockCommentID anyway, the only thing that doesnt work is that if there is no Value1 or Value2, they are worth Null. and unlike in Access, if you do 'Hi' + Null, it doesnt result in 'Hi'. it results in Null(and thats really really stupid. anyway. i'll find a solution to that... at worst i'll find it tomorrow since i dont seem to be able to see my smallest mistakes... loloh and... i know its a really small amount of code for a whole day of labor... lol but my SQL notions are pretty far behind me, so i searched for a long time instead of actually coding.

<i>is this for any particular database platform?</i>Access... but i'm just trying to make the select work on Query analyser<i>are the pipe chars mixed in with the comment or do the fields with pipes only contain pipe chars?</i>yeah. it's mxed. for example:Minimum | boxes of | uniti have to replace those two pipes with Value1 and Value2<i>do you have control over these delimiters, can you decide to change delimiters or is that beyond your control?</i>boyond my control. i dont have insert, update an drop permissions on that DB. otherwise, i would have changed one of the pipes for a !, and i would have used replace(replace(blah, '|', Value1), '!', Value2)my only other comment at this time is that the problem as posed would likely be easier to deal with on the code side, the easy db side fix would probably require a stored procedure or function to cope with the string parsing.

ok, i have to work with 2 tables, called "StockComment" and "StockDetailComment"StockComment: StockCommentID CommentType (i dont need that right now) CommentStockDetailComment: StockDetailCommentID StockDetailID (i dont need that right now) StockCommentID (foreing key) Value1 Value2So, what my query has to do: in the table StockComment, the Comment field contains a varchar(60). these are strings that either contains 0, 1 or 2 "|" (pipes). So my query has to replace those pipes with the Value1 and Value2 fields. so if theres no pipe, the Comment is returned as is, if there i one pipe, it replace by Value1, and if there is 2 pipes, the first is replaced with Value1 and the second is replaced by Value2.the 2 tables are linked by the stockCommentID. so the query has to check if there is some pipes in the string, and if there is some, take the values in the other table, and replace the pipe(s) with them.also, the query has to do this job for all rows of the table StockComment, because the results will be used to fill a comboBox.I'm really lost on this one! Plz help me!

You simply have to create a combined(i dont know if thats the correct english term, but anyway) primary key, composed of the userID and the pollID, or itemID, or whatever you want to name the object ppl want to vote for. that way, a user can vote for multiple subjects, su subject can be voted by multiple users, but a user wont be able to vote multiple time for the same subject, because it would be trying to create a duplication of the primary key. i you want to let the user to change his vote, then you have to do it with an update query.