# Business Logic — modUtil.FormatNumberForSql() [9 LOC]

| Field | Value |
|-------|-------|
| Fully Qualified Name | `modUtil.FormatNumberForSql` |
| Layer | Utility |
| Module | `(unknown)` (Package: `vba-source/modUtil.bas`) |

## 1. Role

### modUtil.FormatNumberForSql()

This utility method converts a VBA `Currency` type value into a string representation that is safe for embedding directly into SQL statements. In VBA/Access, the `Str()` function always uses a period (`.`) as the decimal separator regardless of the host machine's Windows regional settings, whereas direct string concatenation (e.g., the `&` operator or `CStr()`) uses the locale's configured decimal separator. On systems with a comma-based locale (such as Vietnamese Windows), concatenating a `Currency` value directly into SQL text silently inserts a comma into the numeric literal, which breaks the SQL statement by shifting value counts and causing syntax errors or incorrect comparisons. This method wraps `Str()` and trims the leading space that `Str()` prefixes for non-negative numbers, producing a clean numeric string like `"1234.56"` suitable for SQL value clauses. It serves as a shared localization-safe number formatting utility called by import and reconciliation routines across the application.

## 2. Processing Pattern (Detailed Business Logic)

```mermaid
flowchart TD
    START(["FormatNumberForSql(n As Currency)"])
    STEP1["Convert Currency to string using Str(n)"]
    STEP2["Trim leading whitespace from result"]
    END_NODE(["Return formatted string"])

    START --> STEP1
    STEP1 --> STEP2
    STEP2 --> END_NODE
```

**Processing description:**

The method performs a straightforward two-step transformation on its single `Currency` parameter:

1. **Convert using `Str()`** — VBA's `Str()` function converts the numeric `Currency` value to a string representation. Critically, `Str()` always uses `.` (period) as the decimal separator regardless of the Windows locale, ensuring the resulting string is SQL-safe. For non-negative values, `Str()` also prepends a leading space (e.g., `Str(100.50)` yields `" 100.50"`).

2. **Trim whitespace** — `Trim()` removes the leading space introduced by `Str()`, producing a clean numeric string ready for SQL embedding (e.g., `"100.50"`).

## 3. Parameter Analysis

| No | Parameter Name | Type | Business Description |
|----|---------------|------|---------------------|
| 1 | `n` | `Currency` | A monetary or numeric value (such as a transaction amount, bank statement line item, or reconciliation variance) that needs to be embedded as a literal in a SQL statement. The `Currency` type provides fixed-point precision to two decimal places, suitable for financial data. |

No instance fields or external state are read by this method.

## 4. CRUD Operations / Called Services

This method performs no data access operations. It is a pure string transformation function that returns a formatted string.

| CRUD | SC / CBS | SC Code | Entity / DB | Operation Description |
|------|----------|---------|-------------|----------------------|
| - | - | - | - | No CRUD operations — pure in-memory string formatting |

## 5. Dependency Trace

### Pre-computed evidence from code analysis graph:

No screen/batch entry points found within 8 hops. Direct callers found: 3 methods.

| # | Caller (Screen/Batch) | Call Chain (Full Path to this Method) | Terminal (SC / CRUD / Entity) |
|---|----------------------|--------------------------------------|-------------------------------|
| 1 | Module: `modImport.RunImport` | `RunImport` -> `modUtil.FormatNumberForSql` | `tblBankStatement`, `tblTransactions` [C] (insert Amount values) |
| 2 | Module: `modReconcile.InsertResult` | `InsertResult` -> `modUtil.FormatNumberForSql` | `tblBankStatementResult` [C] (insert variance Amount) |
| 3 | Module: `modReconcile.RunReconcile` | `RunReconcile` -> `modUtil.FormatNumberForSql` | `tblBankStatement` [R] (select Amount for fuzzy matching) |

**Usage context:**

- **`modImport.RunImport()`** — During Excel file import, this method formats transaction `Amount` fields before embedding them in `INSERT` statements for `tblBankStatement` and `tblTransactions` tables, ensuring locale-independent decimal separators in the SQL literals.

- **`modReconcile.RunReconcile()`** — During bank statement reconciliation, this method formats the incoming transaction's `Amount` in a `SELECT` query that performs fuzzy matching (same amount, date within ±1 day) against the `tblBankStatement` table.

- **`modReconcile.InsertResult()`** — When writing reconciliation results, this method formats the `variance` (a `Currency` representing the difference between a bank statement amount and a transaction amount) as a SQL-safe string in the result insert statement.

## 6. Per-Branch Detail Blocks

**Block 1** — [SEQUENTIAL] `(no branches)` (L34–L42)

> The method has no conditional logic or branching. It executes a single deterministic transformation pipeline.

| # | Type | Code | Business Description |
|---|------|------|---------------------|
| 1 | CALL | `Str(n)` | Convert Currency to SQL-safe string using VBA's `Str()` function, which always uses `.` as the decimal separator regardless of locale |
| 2 | CALL | `Trim(...)` | Remove the leading space that `Str()` prefixes for non-negative values (e.g., `" 100.50"` becomes `"100.50"`) |
| 3 | RETURN | `Trim(Str(n))` | Return the trimmed numeric string to the caller for embedding in SQL |

## 7. Glossary

| Term | Type | Business Meaning |
|------|------|------------------|
| `Currency` | Data type | VBA fixed-point numeric type with 15 digits of precision and 4 decimal places, used for financial and monetary values to avoid floating-point rounding errors |
| `Str()` | VBA function | Converts a number to a string; always uses `.` (period) as the decimal separator regardless of Windows locale; prepends a space for non-negative numbers |
| `Trim()` | VBA function | Removes leading and trailing whitespace characters from a string |
| Locale | Concept | Windows regional settings that determine the decimal separator (e.g., `.` in US/English, `,` in Vietnamese/German) |
| `tblBankStatement` | Table | Bank statement import record — stores imported bank transaction data including Amount, TxnDate, and BankRefNo |
| `tblTransactions` | Table | Core transaction ledger — stores branch-level transactions with Amount, TxnDate, and RefNo |
| `tblBankStatementResult` | Table | Reconciliation result store — holds matched/unmatched transaction pairs with status and variance amounts |
| Fuzzy matching | Business process | Reconciliation strategy that matches transactions by similar amount and nearby date (±1 day) rather than exact key match |
| Variance | Field | The numeric difference between a bank statement amount and a matched transaction amount; indicates over/under payment or discrepancy |