# Posts by mohsin74

• ## Sumproduct

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?

thanks

• ## Sumproduct

Re: Sumproduct

Hi

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

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

• ## Sumproduct

Re: Sumproduct

hi

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.

Thank you

• ## Sumproduct

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:;
SUMPRODUCT((A2:A104={16,17,19,21,23,33,34,36,38,52,53,515,516,518,11})*B2:B104)
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.

Thank you

• ## "file System Object"

Re: &quot;file System Object&quot;

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

• ## Change Hex Number To Dec Number

Re: Change Hex Number To Dec Number

Hi

Yes, text field to hex format..

thank you

• ## Change Hex Number To Dec Number

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
Mohsin

• ## Change Hex Number To Dec Number

Hi there

I want to change a Hex number to dec number in ms access, how can i do that?
in excel, just apply =hex2dec(range), .

cheers
Mohsin

• ## "file System Object"

Re: &quot;file System Object&quot;

hI Kenneth

thanks again for your reply, here i'm attached the sample layout for 3 field from 2 separate sections....

br,
mohsin

• ## "file System Object"

Re: &quot;file System Object&quot;

Hi kenneth

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.

• ## "file System Object"

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.

• ## Search And Import As Field

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.

• ## Search And Import As Field

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.

• ## Scan And Count

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