Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. rodrunner79

    Passing varibles to a CreateParameter Method. Is this Possible?

    Sorry forgot to trim the "". The value adInteger stored in the table/recordset does not include the quotes so when I pass it to the method as an argument rs!CustomField2, I expect it to return it as adInteger but maybe I'll store numbers instead and try it as you suggest. I will let you know...
  2. rodrunner79

    Passing varibles to a CreateParameter Method. Is this Possible?

    The actual values I'm deriving from table and passing to the arguments are: CreateParameter ("SessionID", "adInteger", adInputParameter, 255, valuefromfunctioncall) I also uploaded the error I'm getting: Can't means won't try.
  3. rodrunner79

    Passing varibles to a CreateParameter Method. Is this Possible?

    To Andrzejek, All the values I will be passing to the arguments of the CreateParemeter method would be table-derived values as if I hard-coded them. Take the following example: Dim prm As ADODB.Parameter Set prm = command.CreateParameter ("@CustomerID", adInteger, adParamInput, 0, 13)...
  4. rodrunner79

    Passing varibles to a CreateParameter Method. Is this Possible?

    ...Dim strSQL As String Dim cnn As ADODB.Connection Dim cmd As ADODB.Command Dim prm As ADODB.Parameter strSQL = "SELECT A.* " & _ "FROM tblSytemUtility AS A " & _ "WHERE A.Category = 'Stored Procedure' " & _ "And A.SPID = " & lngUtilityID &...
  5. rodrunner79

    Increment Number in Table/Column Value

    The fact: Running an Access 2007 database front-end and SQL backend where tables are linked via DSNLESS ODBC. The problem: I have to generate a order line number in a query for each PO/order line in one query and derive the starting line number from another table. For example, I have a table...
  6. rodrunner79

    Move MSys Tables to Backend.

    Access has bunch of MSys* tables that were/are created when the DB is created. Is there anyway, I can move these to another backend database and link to them so that I can delete them on my front-end app? The reason why I'm asking is because I have a lot of objects and code on my front-end...
  7. rodrunner79

    Need Help with Report and Grouping

    Hello, I've been scratching my head as to how I'm going to do this on a report and have been trying different ways but to no avail. So I decided to seek help from a site that never fails. Please bear with me as I try to explain it what I need the best way I can. Basically, I have a report...
  8. rodrunner79

    CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

    Thank you! That did it. If you don't mind, could you explain to me what this query is doing. I know it's a common table expression or temp table but never really used one. I just need to understand it. Can't means won't try.
  9. rodrunner79

    CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

    Why can't you edit these post.... Let me rephrase my last sentences: Basically from a business operation standpoint, F_CD would be my indicator of whether the document record is new or has been modified. If F_CD is 1, it's new and if it's greater than 1, then it has been modified; therefore, I...
  10. rodrunner79

    CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

    Hi imex, First, thanks for your response. I tried your suggestion and the query returned only records where (t.F_CD > 1) and (t.P_CD = 3). Your query is only returning Row 2 and 5. That's not what I'm expecting but that's my fault for not properly articulating what I expect the results to...
  11. rodrunner79

    CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

    Sorry, wrong query. Here's the right query: SELECT A.REC_ID, A.VERS_NO, A.F_CD, A.P_CD, A.S_CD FROM Table1 A, (SELECT CASE WHEN (F_CD > 1 AND P_CD = 3) THEN MAX(VERS_NO) ELSE 0 END AS MAX_VERS_NO, REC_ID, VERS_NO FROM Table1 GROUP BY REC_ID, DOC_VERS_NO, F_CD, S_CD) B WHERE A.REC_ID =...
  12. rodrunner79

    CASE WHEN (FIELD1 = 1 AND FIELD2 = 2) THEN SELECT THE MAX OF THIS RECORD FROM THIS FIELD

    Hi there, I've been trying to find out how to do this but I can't get the right results so I am resorting to tek-tips to help me out, as I've always had positive experience in these forums. Just to preface the problem, here's my table of data: REC_ID F_CD P_CD S_CD VERS_NO A 1 5...
  13. rodrunner79

    How to auto-generate a 3 character value...

    Okay here's my task: I have a table consisting of 5 columns: FacilityID, , VendorPartNo, VendorCode, UnitOfMeasure, CommodityCode and Suffix Assuming that all records have values for all of the first five columns and the last column, Suffix is NULL. I want to create an update query that...
  14. rodrunner79

    How do I build an SQL Select Statement that returns a IS NOT NULL?

    Oh, one more thing. I haven't been to this Forum and a while but everytime I do, I'm always treated with courtesy and respect. Always get my questions answered and issues adressed and resolved. Just want to acknowledge the many inteligent users that goes on this site. If it wasn't for you...
  15. rodrunner79

    How do I build an SQL Select Statement that returns a IS NOT NULL?

    Hi George, That was perfect. You, sir, are a gentleman and a scholar. Thank you very much! I took a SQL class a couple of years back but never really applied what I learn in business operations so I forgot most of it. Now, I need to polish up. Slowly but surely I'll get the hang of it...
  16. rodrunner79

    How do I build an SQL Select Statement that returns a IS NOT NULL?

    Thanks, Ian. But that didn't solve it. Ok, I looked closer at the data and turns out that the records themselves are not null values but rather they are spaces. I counted the number of spaces and then tried to filter them out by using ' ' inside my WHERE clause but still no luck. Here's the...
  17. rodrunner79

    How do I build an SQL Select Statement that returns a IS NOT NULL?

    Ok, here's the problem: I have to 2 tables, one has a packslip # (Table A) and one as an invoice # (Table B). They are related by the PO number. I need to build a query where the return set contain the Item Code, Packslip # and Invoice # where both packslip # and invoice # are not null...
  18. rodrunner79

    Create an autonumber with First Character as Prefix (e.g. A000001)

    Geoff: No, I'm calling the function in an action query. It looks like this: INSERT INTO tblFtpOrdPrep ( UnqId, ReqId, ItmCd, ReqQty, UoM, LineAmt, AltUnqID ) SELECT [ReqID] & [DlvToLoc] & fJulianDay() & Format(Date(),"YY") & Left([ItmCd],8) AS UnqID...
  19. rodrunner79

    Create an autonumber with First Character as Prefix (e.g. A000001)

    Hi Geoff, Again, thanks for your replies. I took your suggestion and used your code. I did some modifications to it or more like enhancement. With the modifications, your function will now add a letter prefix before the numbers when inserting the autonumber to a record. I also formatted...
  20. rodrunner79

    Create an autonumber with First Character as Prefix (e.g. A000001)

    First, thank you for your response. I am aware of Access data types, especially Long Integer as I've used it countless times. The limitation is not on my side but rather at the party I am sending the information to. I am currently sending a 23-chars field along with other fields as a Purchase...

Part and Inventory Search

Back
Top