Posts by mohsin74

    Re: Sumproduct

    Thank you Will for your support,

    One thing, from my queries, certain field2 is empty, and i would like to replace the empty row with 0. I put this command in the field1;

    select Iif(sum(field1) is null,0,sum(field1)),

    but still the queries, not count the empty for field 2,

    any suggestions?


    Re: Sumproduct


    I' relised that, in criteria, I can't define "10,11,13.....", but have to do per line;

    instead of one line, i have to do per line. It's any other way to do ? or any limitation in acces for defining criteria?

    thank you

    Re: Sumproduct


    My queries as below;

    SELECT Sum([qrTogetTotalValue]![Total]) AS CG1
    FROM qrTogetTotalValue
    WHERE ((([qrTogetTotalValue]![ClearCode])="10,11,13,15,17,21,22,24,26,34,35,203,204,206,B"));

    the result for CG1, is 0, how can i check, what is wrong of this queries,??
    Total is field1, and clearcode is field2.

    please guide,

    Thank you

    Hi there

    I'm using sumproduct to grouping the values, so how can i used it in access?
    I have a table, then i have to make a query using sumproduct function.
    for example, in excel:;
    where A2:A104= the range which to group the 16,17,19,21,23,33,34,36,38,52,53,515,516,518,11, (numberinHex)
    and B2:B104 = the range contain the value, which it will sum to get the total value for this group.

    Please guide,

    Thank you

    Re: "file System Object"

    WoW!..Thanks so much for taking the time to help me out.

    Regards of your question, 1 , the anser is Yes. .

    41 18 GTTK1 41 0

    since it's appeared in diffrent of command group, (ZNSI for LINK and ZNCI for LINK SLC), it have to combine to get SLC to match with link.

    So Q2, you'r right, it's only need the last part/digits which is SLC field to match with LINK, as below result;

    41 18 GTTK1 0

    Appreciate for your help,,

    Re: Change Hex Number To Dec Number

    In detail;

    My value = 319,A03,B13,16, which is in text format. The numbers actually are
    in hex value. What i need to do now is, convert the value from text to hex
    then to decimal,

    i'm using below expression for converting to hex, but is wrong..:)

    CCDec: Val(Replace([tblclearcode]![CLEARCODE_ID],",",""))

    Thank you

    Re: "file System Object"

    Hi kenneth

    Thanks for your reply

    My log data, is containing various of information in text file. The problem is, the log file is not in delimited format, or tab format, is not in same order. If i setting one data, I will lose other's data, due to the field is not in same order.

    I'm attached the sample data.

    Hi All

    Can someone, explean how the "File System object" works in Excel?

    I have a various information in one log file, which need to import to excel. So it's i need to declare all fields i need? or how?

    appreciate for your help and explanation.

    Re: Search And Import As Field

    Hi Norie

    Thank you for your help first;
    Yes I can attach the log as an example, and yes also, i want to import it to excel, base on field i needed.

    I'm attached here the SampleData. From log, the <ZNCI; and ZNSI:NA0; is the MML command, between the command excuted is the information. For example in ZNCI, LINK, LINK SET, PCM-TSL, and so on..

    So my objective here is to extracted the LINK, LINK SET or any field i need and import it to excel.

    Appreciate for your help

    Hi all

    I'm new in VBA. Thanks for any help regards this;

    I have a log file with this format;
    MML Command;
    Field1 Field2 ...
    Command Executed;
    DIfferent MML command different fields. I need to search first the MML command, then search again the Field1 Field2 as I need. Since the format of field between MML command is different each other's, I cannot import it to excel.

    So How can, I start to do the vba, to search a MML command i need, and then field1 etc.. and stop at command executed, ?? Any idea as a start.

    Appreciate for your help.

    Hi Expert

    I'm very poor in vba, need your help to guide me.

    I have one input sheets which contain like this:

    A B C D E F G
    xyz 0 date&time 1 0 1 Sum(D1:E1:F1)
    xyz 1 date&time 2 1 10 Sum(D2:E2:F2)
    xyz 3 date&time 6 9 12 Sum(D3:E3:F3)

    and so on..

    And what i need to do is:

    1. Sum(D:E:F) from each column and put in row G.
    2. Create new sheets and perform the below task

    A B C (result)
    Group 1 = 0 (base on row b) date&time
    Group 2 = 1&3&5&7&.... date&time
    Group 3 = 6&10&.... date&time

    I have to scan from the input sheets, the 0 (from row B), date&time, and value from row G put it in C. Then second column, the group 2, scan the 1&3&5&7&.... , date&time, sum the value from row G, and put it in C.

    At least if you guys can guide me for Group 1 and 2, is very appreaciate.

    br, mohsin