Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: How to print Number in report without slash (/)

    Hi gents,

    I have a table called Product_Master that hold some records with below fields and holds below records.
    BarcodeNumber (Text)
    Description (Text)
    BrandName (Text)
    PurPrice (Number)
    SalsePrice.(Number)
    Etc…


    The barcode number are like:
    524 00 258/J
    524 00 258/2
    465 568 5241/O
    0698 55 2425
    524 00 258/7

    My header invoice form F_SalesInvHead with sub-form F_SalesInvFoot is based on one-to-many relationship that I set on InvNum field Both the header and footer form's record source are table named T_SalesInvHead and T_SalesInvFoot.respectively.

    I pulling data from Product_aster tbl and entering into T_SalesInvFoot (thru sub-form).

    T_SalesInvFoot also holds similar fields like Product_Master (BarcodeNumber, Description etc..)

    The invoice report generated thur a command btn is based on a simple query that displays records of a particular invoice. The invoice report fields are Barcodenumber, Discription, SalesQty, SalesPrice etc.

    Every invoice prints smoothly with Barcode number, Description, SalesQty, SalesPrice Amount as entered in the sales invoice footer.

    Now my user needs barcode number to appear without slash (/) and thereafter characters.

    e.g. if barcode is 465 568 5241/O for the item, should appear in invoice 465 568 5241 only and so on.

    Slash may be after 7 digit or 9 digit or some time there may not be any slash in barcode number entered in the record. But in any case, in invoice print barcode number should be displayed without Slash (/) and thereafter characters if any.

    I put my invoice report query code here.

    SELECT T_SalesInvHead.InvNum, T_SalesInvHead.InvDate, T_SalesInvHead.CustomerCode, T_SalesInvHead.CustomerName, T_SalesInvHead.SalesAddress, T_SalesInvHead.MobileNo, T_SalesInvHead.UserName, T_SalesInvFoot.ProductCode, T_SalesInvFoot.Productname, T_SalesInvFoot.BarcodeNumber, T_SalesInvFoot.SalesQty, T_SalesInvFoot.SalesPrice, T_SalesInvFoot.Amount, T_SalesInvHead.SalesDiscount, T_SalesInvHead.WAmount, T_SalesInvHead.CrDr
    FROM T_SalesInvHead INNER JOIN T_SalesInvFoot ON T_SalesInvHead.InvNum = T_SalesInvFoot.InvNum
    WHERE (((T_SalesInvHead.InvNum)=[Forms]![F_SalesInvHead]![InvNum]))
    ORDER BY T_SalesInvFoot.Productname;

    How can I do that? Any idea?

    Please extend your help.

    Thanks.
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink re

    Make an alias of your BarcodeNumber field and use it with an instr function
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks.

    Where and how to create. I never created before. Can you put some light on it please?

    Regards,
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally Id use a function to tidy it up
    where you deploy the function is up to you..
    ..it could be immediately after the barcode is captured, so you are onkly storing the tidied up barcode
    it could be in the report/forms usign the barcode

    of the two I think you would be better off in the data capture area, so you only have to use the function once per barcode, rather than repeating the same function call in queries, forms or reports.

    before binning the "/" I'd want to make certain that I wasn't throwing away useful data.. I'd want to know precisely what the /x means.. it could well be a checkdigit which may be useful to make sure the barcode is valid.

    Code:
    Private Function TidyUpBarcode(BarcodeValue As String) As String
    'tidies up a barcode
    'some barcodes can be terminated in "\" and one or more other characters
    'we want a function which will only remove the "/" and subsequent characters
    Dim EndAt As Byte 'we need a var to store where to chop the string at
    EndAt = InStr(BarcodeValue, "/") 'find out where the first / character is
    If EndAt > 0 Then 'did we find a '/' character in the barcode
      TidyUpBarcode = Left$(BarcodeValue, EndAt - 1) 'if so chop of that. and nay reamining charcters
    Else
      TidyUpBarcode = BarcodeValue 'assign the imput parameter as the default
    End If
    End Function
    HTH

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    re

    You could shearch it in the help file's

    OR Online






















    Code:
    iif(instr(T_SalesInvFoot.BarcodeNumber ,"/")>0,left(T_SalesInvFoot.BarcodeNumber,instr(T_SalesInvFoot.BarcodeNumber ,"/")-1),T_SalesInvFoot.BarcodeNumber)
    AS BarcodeNumber
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Wink Not in the line off

    healdem it's an solition and it will work, but it's not in the line that the question was asked

    Clearly it's an SQL and sticking your solution in a function will not realy solve the problem at hand.

    Otherwise you allso could refacture the dataTable/fields in 2 fields in stead of 1 (to split the barcode field )
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Marvels
    Otherwise you allso could refacture the dataTable/fields in 2 fields in stead of 1 (to split the barcode field )
    +1

    I thought this when I read the Q but coulddn't be bothered typing it out

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Marvels
    healdem it's an solition and it will work, but it's not in the line that the question was asked

    Clearly it's an SQL and sticking your solution in a function will not realy solve the problem at hand.

    Otherwise you allso could refacture the dataTable/fields in 2 fields in stead of 1 (to split the barcode field )
    each to their own

    I don't read it as purely an SQL problem...

    primarily as the only way I can think of handling it in SQL is to use an dreaded IIF statement. which potentially will have horrendous performance implications (mind you putting the code into a user written function isn't too clever either)

    However putting it into a function means that the code can be used in SQL or a form / report or whatever else the OP wants to use it. If its going to be used then in my view it should be in the data capture form and the /x discarded..

    ...unless it has some meaning. And I suspect in this case it almost certainly does as Ashfaque regularly asks questions here to do with his stock control / till system. I suspect the /x will identify which of the cases / pack types a product scan is

    ..the /x could be stored in another column.. but in which case the candidate function should be split(), rather than instr()

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks gents.

    The /x is a simbol stands to know salesman that the item being sold are Original or Janapnese or Chinese. Something like this.

    524 00 258/J means its a Japanese item
    465 568 5241/O means it is Original item
    524 00 258/1 first quality in duplicate

    This is self-created symbol to let the seller know. But dont want to print it on invoice. Purchaser also known that it is duplicate or whatever but purchaser also dont want it last part of number (slash and thereafter part)

    Regards,
    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Yeah...it helped me...

    I just placed one unbound box in details section of report where there was bound text box for Barcode number and placed above code:

    =IIf(instr(BarcodeNumber ,"/")>0,left(BarcodeNumber,instr(BarcodeNumber ,"/")-1),.BarcodeNumber)

    then I simply set property of bound Barcodenumber field to Visible = False.

    Done...

    My sincere thanks to healdem, Marvels and Pootle flump for their valuable time.
    With kind regards,
    Ashfaque

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •